Splitting the values in a column on a comma

[jeepxo]

Most of my experience is in VB and Java and have always handle this in my code.

I thought that it might be faster to do it right in SQL though.

I have a column called Name that contains firstname,middlename, lastname.

I need to display them in separate fields in an ASP page. Normally I would grab a record set and use SPLIT(record,',')
and create an array.

Is there a way of doing this right in sql?

I'm think something like

Select split(name,',',1) as First, split(name,',',2) as middle, split(name,',',3) as last

should be in sql as well, I just can't find the syntax.
Any help would be appreciated.

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[jeepxo]

I can do this if there is only 1 comma. But what if I don't know how many commas there are?

SELECT     RTRIM(SUBSTRING(NAME, 1, CHARINDEX(',', NAME) - 1)) AS lastName, LTRIM(SUBSTRING(NAME, CHARINDEX(',', NAME) + 1, LEN(NAME))) 
                      AS FirstName
FROM         SD_STUDENT

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[travisbrown]

The simple way:

SELECT col1 = SUBSTRING(name,(n*s)+1,w), col2 = ....

n = element index
s = length. You'll have to do some fancy LEN functions to determine the length
w = field length


You'll run into a problem if your sting only has two elements; the last name will become the middle name. The select degrades nicely if you walk off the end. SUBSTRING just returns an empty string when out of bounds.

This is a pretty simplistic approach though. This might be a call for a cursor. (Ugh!). Below is some modified code I used once. I'm sure there might be a non-cursor solution.

DECLARE @arr varchar(8000), @select varchar(8000)
DECLARE @a int, @b int, @c int, @d int
DECLARE c CURSOR FOR SELECT * FROM yourtable

SET @select = 'SELECT '
SET @d = 0

OPEN c
FETCH c INTO @a, @arr

WHILE (@@FETCH_STATUS = 0) BEGIN
 SET @b = 0
 SET @c = DATALENGTH(@arr)/15 
--you might have to perform some jiggerypoke with LEN to determine the length in the line above.
WHILE (@b<@c) BEGIN
SELECT @select = @select + 'name'+CAST(@d AS VARCHAR)+' = ' + QUOTENAME(RTRIM(SUBSTRING(@arr,(@b*15)+1,15)),'"')+','
SET @b = @b +1
SET @d = @d +1
END
FETCH c INTO @a, @arr
END
CLOSE c
DEALLOCATE c

SELECT @ select = LEFT(@sel,DATALENGTH(@sel)-1)
EXEC(@sel)

[\code]

 

[gmmastros]

You an create a user defined function to accomplish similar results.

First, the UDF.

Alter Function dbo.Split
	(
	@CommaDelimitedFieldNames Varchar(8000), 
	@Character VarChar(20)
	) 
Returns @Tbl_FieldNames 
Table 	(
		Id Integer Identity(1,1),
		FieldName VarChar(100)
		) 
As 
Begin 
 Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @Character 

 Declare @Pos1 Int
 Declare @pos2 Int
 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@CommaDelimitedFieldNames)
 Begin
 Set @Pos1 = CharIndex(@Character,@CommaDelimitedFieldNames,@Pos1)
 Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(100))
 Set @Pos2=@Pos1+1
 Set @Pos1 = @Pos1+1
 End 
 Return
End

Then, To use this function...

Declare @Name Varchar(100)
Set @Name = 'George, W., Bush'

Select * From dbo.Split(Name, ',')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom