Split String at Comma into 2 fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Split String at Comma into 2 fields

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-05-20 : 12:27:33

I have a first name and second name in the same field. (Usually they will be split by a comma - if no comma use the space a separator)
I have looked at the forums to find useful code but it has not applied or worked for my scenario.

Any ideas using the sample data below?

-----------------------------------------------
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE TblFullName(Col1 varchar(255))
GO

INSERT INTO TblFullName(Col1)
SELECT 'MCCRAVE,ELIZABETH' UNION ALL
SELECT 'Smith ,Bill' UNION ALL
SELECT 'JONES, CATHERINE' UNION ALL
SELECT 'FORSTER SHARON extra'
GO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-20 : 13:15:39

There are probalby better ways, but I put together real quick:

DECLARE @TblFullName TABLE(Col1 varchar(255))

INSERT INTO @TblFullName(Col1)
SELECT 'MCCRAVE,ELIZABETH' UNION ALL
SELECT 'Smith ,Bill' UNION ALL
SELECT 'JONES, CATHERINE' UNION ALL
SELECT 'FORSTER SHARON extra'


SELECT 
	RTRIM(LTRIM(CASE 
		WHEN CHARINDEX(',', Col1) > 0 
			THEN SUBSTRING(Col1, 1, CHARINDEX(',', Col1) - 1)
		WHEN CHARINDEX(' ', Col1) > 0
			THEN SUBSTRING(Col1, 1, CHARINDEX(' ', Col1) - 1)
		ELSE
			Col1
	END)) AS FirstName,
	RTRIM(LTRIM(CASE 
		WHEN CHARINDEX(',', Col1) > 0 
			THEN SUBSTRING(Col1, CHARINDEX(',', Col1) + 1, LEN(Col1) - CHARINDEX(',', Col1))
		WHEN CHARINDEX(' ', Col1) > 0
			THEN SUBSTRING(Col1, CHARINDEX(' ', Col1) + 1, LEN(Col1) - CHARINDEX(' ', Col1))
		ELSE
			''
	END)) AS LastName
FROM
	@TblFullName

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-20 : 15:49:31

C'mon Lamprey, you could have been a little more imaginative with the names.

Amanda Hugginkiss? Phil Mc Kracken?

Albert Koholic

An infinite universe is the ultimate cartesian product.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-20 : 18:01:38

I know, I know... I was lazy so I copied the OP's data.. :)

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-05-21 : 04:47:03

Brilliant working like a dream :)
Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-05-21 : 10:31:49

Xavier Onassis

e4 d5 xd5 Nf6