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

SQL Server 2000 Forums

SQL Server Development (2000)

Split function

Author

Topic

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-15 : 02:04:07

I have been reading the user defined function, Split, but do not really understand the split method. Can anyone tell me what exactly will "Data" in the split function return?

Will it return a insert statement? Thanks



CREATE FUNCTION 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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 02:30:23

Execute it like this. 2 columns will be returned. Column Id is the sequence of data in the @RowData. Column Data will be the data itself.

select 	*
from	dbo.SPLIT('1,4,3,5, 35 , 34', ',')
Result :
Id          Data                                                                                                 
----------- ----
1           1
2           4
3           3
4           5
5           35
6           34

(6 row(s) affected)

-----------------
'KH'

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-15 : 02:37:46

If my stored procedure is as shown below, what will the data be?


CREATE PROCEDURE [ADDITEMPROM] 
(
@PROMOTIONID INT,
@ITEMIDS VARCHAR(500)
)

As

INSERT INTO ITEMPROM
(PROMOTIONID, ITEMID)
SELECT @PROMOTIONID, [Data]
FROM DBO.SPLIT(@ITEMIDS, ',')
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 02:49:14

Data will be individual item specified in @ITEMIDS
for example if @ITEMIDS is 'abc,def,ghi', the SPLIT function will split the string 'abc',def',ghi' into 3 individual string 'abc', 'def', 'ghi'

-----------------
'KH'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 02:51:56

Try this . . .
exec ADDITEMPROM 100, 'abc,def,ghi,jkl'
select * from ITEMPROM

-----------------
'KH'

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-15 : 03:21:02

http://sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources