using split function - 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

General SQL Server Forums

New to SQL Server Programming

using split function

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2011-12-01 : 13:10:55

Hi All,

I have this table that has

Col1 col2 col3 col4 col5

1 abc def 455 abc, gte, pqr
2 jjj ooo 976 kew, pwq, yer

I saw the split function on internet and executed that one on my query analyzer, now I can split the comma delimited fields to rows. I guess I don't know how to use this split function.

I want something like this in my ouput

Table A

Col1 col2 col3 col4 col5

1 abc def 455 abc
1 abc def 455 gte
1 abc def 455 pqr
2 jjj ooo 976 kew
2 jjj ooo 976 pwq
2 jjj ooo 976 yer

My split function that I got from internet is like this

ALTER FUNCTION [dbo].[Split]
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

I am doing somethig like this and I am getting an error

select col1, col2, col3, col4, dbo.split(col5)
from table A

to get the result that I displayed above, but I am getting an error.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-01 : 13:30:41

Your function signature requires two parameters, so you have to provide the second parameter. Also, it returns a table with two columns, so you have to treat it like a table. So, this should work:


select col1, col2, col3, col4,Data 
from table A CROSS APPLY dbo.split(col5,',')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-01 : 13:32:56

BTW, the split function you are using has a while loop, which may cause performance issues if your tables are large. One of the more efficient ones is described here: http://www.sqlservercentral.com/articles/Tally+Table/72993/. The function is in Fig. 21.

anjali5
Posting Yak Master

121 Posts

Posted - 2011-12-01 : 13:41:39

what is "data" here in the select columns

select col1, col2, col3, col4,Data from table A CROSS APPLY dbo.split(col5,',')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-01 : 14:05:34

"Data" is the name of the column returned by your Split function:

ALTER FUNCTION [dbo].[Split]
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
) 
RETURNS @RtnValue table 
(
Id int identity(1,1),
Data nvarchar(100)
) 
AS 
BEGIN 
Declare @Cnt int
Set @Cnt = 1

If you run the following code, you will see both columns from the split function in your output:


select col1, col2, col3, col4,T.* 
from table A CROSS APPLY dbo.split(col5,',') T

anjali5
Posting Yak Master

121 Posts

Posted - 2011-12-01 : 15:47:20

Thanks Sunita!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources