Split field into two based on "\" character 1

[KARR]

All,

I have a table with a field called "Name" containing date in this format:

LastName\FirstName

Example:

Record1 Brown\Joe
Record2 Smith\Rob

and would like to have it split the names and create two new fields:

Example:
LastName FirstName
Record1 Brown Joe
Record2 Smith Rob

I looked through the SQL Books online with no success, any ideas?

 

[jbenson001]

Great, glad it is working

 

[KARR]

I have another field thats seperated like the one above, except that instead of a single '/' there are two:

05/13/2005

I know I can parse from the left and get the 05, and then parse the right and get the 2005, but how to get the 13? And then in some instances it may be a single digit in middle.

Thanks

 

[JamesLean]

If these are datetime values, you can use DAY, MONTH and YEAR functions to get respective parts.

--James

 

[KARR]

Thats what I was thinking, and I forgot to add in the post that sometimes the 2005 value is 005. Dont ask, I didnt designe the old database.

I dont think the Year function would recognize 005 as 2005?

 

[JamesLean]

One way would be to use a function that takes a delimited string and gets the selected portion from it. I found this which I wrote a little while ago which should work for you:

IF OBJECT_ID('fnGetDelimStringPortion') IS NOT NULL
	DROP FUNCTION fnGetDelimStringPortion
GO

CREATE FUNCTION fnGetDelimStringPortion (
	@delimString varchar(1000),
	@delimiter char(1),
	@whichPortion int
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @numDelimsToDelete int,
	@ctr int,
	@output varchar(1000)

IF RIGHT(RTRIM(@delimString), 1) <> @delimiter
	SET @delimString = @delimString + @delimiter

SET @numDelimsToDelete = @whichPortion - 1

SET @ctr = 1

WHILE @ctr <= @numDelimsToDelete
BEGIN
	SET @delimString = STUFF(@delimString, 1, CHARINDEX(@delimiter, @delimString), '')
	SET @ctr = @ctr + 1
END

SET @output = CASE WHEN CHARINDEX(@delimiter, @delimString) > 0 THEN LEFT(@delimString, CHARINDEX(@delimiter, @delimString) - 1) ELSE NULL END

RETURN @output
END
GO

DECLARE @s varchar(50)

SET @s = '25/05/2005'

SELECT @s,
	dbo.fnGetDelimStringPortion(@s, '/', 1),
	dbo.fnGetDelimStringPortion(@s, '/', 2),
	dbo.fnGetDelimStringPortion(@s, '/', 3)

--James

 

[KARR]

Thanks, I will give that a try and see how it works.

Before you posted, I did some more reading and testing and found that a combo of Substring and CharIndex did the trick as well, but in one line.

Substring (OldDate, CharIndex('/',OldDate,1)+1, CharIndex('/',OldDate,4) - CharIndex('/',OldDate,1)-1) as Day

 

[adamroof]

Just my 2 pennies... had a whole bunch of characters to modify and used Select to make sure first, then changed queries to updates...

SELECT ContactID, ContactLName, ContactFName,
(LTRIM(SUBSTRING(ContactLName,CHARINDEX(',',ContactLName,1)+ 1,20))) AS Found FROM hs_tblContact 
WHERE (ContactLName LIKE '%,%' AND ContactLName NOT LIKE '%\%' AND ContactLName NOT LIKE '%attn:%')
	AND ContactFName IS NULL ORDER BY ContactID DESC 

SELECT ContactID, ContactLName, ContactFName,(RTRIM(SUBSTRING(ContactLName,0,CHARINDEX(',',ContactLName,1)))) AS Found
FROM hs_tblContact 
WHERE (ContactLName LIKE '%,%' AND ContactLName NOT LIKE '%\%' AND ContactLName NOT LIKE '%attn:%' AND ContactLName NOT LIKE '%, Jr%')
ORDER BY ContactID DESC 

UPDATE hs_tblContact SET ContactFName = (LTRIM(SUBSTRING(ContactLName,CHARINDEX(',',ContactLName,1)+ 1,20)))
WHERE (ContactLName LIKE '%,%' AND ContactLName NOT LIKE '%\%' AND ContactLName NOT LIKE '%attn:%')
	AND ContactFName IS NULL  

UPDATE hs_tblContact SET ContactLName = (RTRIM(SUBSTRING(ContactLName,0,CHARINDEX(',',ContactLName,1))))
WHERE (ContactLName LIKE '%,%' AND ContactLName NOT LIKE '%\%' AND ContactLName NOT LIKE '%attn:%' AND ContactLName NOT LIKE '%, Jr%')

SELECT ContactID, ContactLName, ContactFName, ContactBName, 
(LTRIM(SUBSTRING(ContactLName,CHARINDEX('\',ContactLName,1)+ 1,30))) AS Found 
FROM hs_tblContact
WHERE ContactLName LIKE '%,%' AND ContactLName NOT LIKE '%, jr%'