Here's something I got from the web, so not claiming credit for the
function.
/*
SELECT * FROM Split('Hella:Cool', ':')
*/
CREATE FUNCTION Split(
@string NVARCHAR(4000), @delimiter CHAR(1)
) RETURNS @results TABLE( [ID] INT IDENTITY(1,1), Items NVARCHAR(4000) )
AS
BEGIN
DECLARE
@index INT,
@slice NVARCHAR(4000)
SET @index = 1
IF @string IS NULL RETURN
WHILE ( @index != 0 ) BEGIN
SELECT @index = CHARINDEX(@delimiter,@string)
IF ( @index != 0 )
SELECT @slice = LEFT(@string, @index - 1)
ELSE
SELECT @slice = @string
INSERT INTO @results (
Items
) VALUES (
@slice
)
SELECT @string = RIGHT(@string,LEN(@string) - @index)
IF LEN(@string) = 0
BREAK
END
RETURN
END
It returns a table so you can select the first part of the name.
Is that any help?
Ade
-----Original Message-----
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: 23 February 2005 22:22
To: CF-Talk
Subject: RE: OT-SQL matching
TABLE1
LNAME FNAME CITY ST
DOE JR JOHN PHOENIX AZ
GATES BILL REDMOND WA
GORE III AL BACKWOODS TN
BRADSHAW TERRY HOUSTON TX
TABLE2
LNAME FNAME CITY ST
DOE JOHN PHOENIX AZ
GATES BILL REDMOND WA
GORE AL BACKWOODS TN
BRADSAW TERRY HOUSTON TX
I will wnt to try to match the two together but in table one there is a
suffix in the last name proceded by a space. need to get those out of there.
I would match all accept for the last row
-----Original Message-----
From: Adrian Lynch [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 23, 2005 4:13 PM
To: CF-Talk
Subject: RE: OT-SQL matching
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:196215
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
