Comma seperate Field - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Comma seperate Field

Author

Topic

jbosco1988
Starting Member

46 Posts

Posted - 2009-05-04 : 19:17:17

If I have multiple records with the First name, last Name, and MI int the same field how do i sepeate them out in 3 different fields.

like:

joe,smith,a
steve,smith,l
sam,jones,t

psycotech
Starting Member

5 Posts

Posted - 2009-05-04 : 21:44:42

Have a look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6206

The below Section Will Cut the values up into a temp table.
Note it has been modified from my solution in the above topic.
You will have to create a pivot Query to insert the values into your table.
This solution works for any number of fields, if you have only a set number like 3 then simply repeat the part in the While loop.

Also this solution was used because i had to include double quotes as escape chars for values containing commas.
read the first couple of posts for a more tricky yet intersting way of doing it, below is the brut force method.

SET @Row = 'Help,Me,Parse'
SET @ColEnd = 1
SET @ColStart = 0
SET @ColINDX = 0
WHILE @ColEnd > 0
BEGIN
SET @ColEnd = CHARINDEX(',', @Row, @ColStart)
SET @Len = @ColEnd - @ColStart
IF @ColEnd < 1 SET @Len = 99999
SET @Col = SUBSTRING(@Row, @ColStart, @Len)
INSERT INTO #Temp
VALUES (@LineINDX, @ColINDX, @Col)
SET @ColStart = @ColEnd + 1
SET @ColINDX = @ColINDX + 1
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-04 : 22:34:01

use any one of the function here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-05 : 00:07:09

declare @str1 varchar(max)
set @str1= 'M001111,M001222,M001333'

SELECT
replace(SUBSTRING(@str1,charindex(',',@str1,v.number),abs(charindex(',',@str1,charindex(',',@str1,v.number)+1)-charindex(',',@str1,v.number))),',','')as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring(',' + @str1, v.number, 1) = ','
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Splitting,delimited,lists

notmyrealname

98 Posts

Posted - 2009-05-05 : 10:58:00

If you have rights to modify the database and there is always 3 items separated by 2 commas you could consider creating three computed columns in the table for FirstName, LastName and MiddleName.

Something like:
Create new column named LastName and set the computed column specification to (LEFT(namefield, CHARINDEX(',', namefield) - 1)) and so on for the other columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources