December 24, 2002 at 4:59 am
DO we have any function in SQL Server 2000 to split a string having multiple comma separated values into as many rows using SELECT statement??
e.g. string like '2541,2542,5362,36585,36884'
December 24, 2002 at 8:26 am
Not that I know of. Possible the REPLACE function will work for you. Something like this:
declare @STR varchar(50)
set @STR = '2541,2542,5362,36585,36884'
select replace(@str,',',char(13))
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 24, 2002 at 9:01 am
You can use replace and a little concatenation to convert to an XML document, then use OpenXML to treat it like a table.
Andy
December 26, 2002 at 4:26 am
Have a look at
http://qa.sqlservercentral.com/scripts/contributions/528.asp
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
December 26, 2002 at 1:00 pm
This will do the work:
DECLARE @delimited_string VARCHAR( 100 )
, @start_position SMALLINT
, @end_position SMALLINT
SELECT @delimited_string = '2541,2542,5362,36585,36884'
, @end_position = 1
CREATE TABLE #list
( start_position SMALLINT NOT NULL
, end_position SMALLINT NOT NULL
)
WHILE @end_position > 0
BEGIN
SELECT @start_position = @end_position
, @end_position = CHARINDEX( ',', @delimited_string, @end_position + 1 )
INSERT #list( start_position, end_position )
VALUES( CASE @start_position WHEN 1 THEN 1 ELSE @start_position + 1 END
, CASE @end_position WHEN 0 THEN LEN( @delimited_string ) + 1 ELSE @end_position END )
END
SELECT SUBSTRING( @delimited_string, start_position, end_position - start_position )
FROM #list
WHERE end_position > 0
DROP TABLE #list
December 27, 2002 at 12:13 am
Mromm! Thanks it really works.. You guys are great.
December 31, 2002 at 3:05 am
You can also use ActiveX scripting within a DTS task and use the SPLIT command of VB, like so;
split(YourString, ",")
This will split a string(which in your case is one dimensional) at the character that is within the quotes. You can also use this to split a multi dimensional string if it has a column and a row delimeter like;
1,2,3,4 | 1,2,3,4 | 1,2,3,4
productarray=split(mystring,"|")
creates a one dimensional array split at the | character then;
for j=0 to ubound(productarray)
MyArray=split(productarray(j),",")
splits it into columns.
Rolf
December 31, 2002 at 9:21 am
Here is the function I use. You can use it withing a join to filter other tables.
Select * from fnSplit('2541,2542,5362,36585,36884', ',')
Returns:
ItemId Item
----------- ---------
1 2541
2 2542
3 5362
4 36585
5 36884
(5 row(s) affected)
CREATE Function fnSplit
(
@List varchar(8000)
, @Delimiter char(1) = ','
)
Returns @Temp1 Table
(
ItemId int Identity(1, 1) NOT NULL PRIMARY KEY
, Item varchar(8000) NULL
)
As
Begin
Declare
@item varchar(4000)
, @iPos int
Set @Delimiter = ISNULL( @Delimiter, ',' )
Set @List = RTrim( LTrim( @List ) )
-- check for final delimiter
If Right( @List, 1 ) <> @Delimiter
-- append final delimiter
Select @List = @List + @Delimiter
-- get position of first element
Select @iPos = Charindex( @Delimiter, @List, 1 )
While @iPos > 0
Begin
-- get item
Select @item = LTrim( RTrim( Substring( @List, 1, @iPos -1 ) ) )
If @@ERROR <> 0 Break
-- remove item form list
Select @List = Substring( @List, @iPos + 1, Len(@List) - @iPos + 1 )
If @@ERROR <> 0 Break
-- insert item
Insert @Temp1 Values( @item )
If @@ERROR <> 0 Break
-- get position pf next item
Select @iPos = Charindex( @Delimiter, @List, 1 )
If @@ERROR <> 0 Break
End
Return
End
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply