split a string into array? 2

[nomi2000]

Hi i wanna implement a function to split a string into array

Declare @SQL as varchar(4000)
Set @SQL='3454545,222,555'
Print @SQL
what i have to do so i have an array in which i have
Arr(0)='3454545'
Arr(1)='222'
Arr(2)='555'

Thanks
Nouman

 

[pgtek]

hi
read this article

http://www.sql.nu/articles/arrtotbl.shtml

pgtek

 

[nomi2000]

Thanks
Alot pgtek

 

[nomi2000]

Hi pgtek
i have done with one string
but what if i have two string
like
string1='34343,555,22'
string2='1,2,3'

now i want to build the xml in this way
<a QUAL='34343' ID='1' />
<a QUAL='34343' ID='2' />
<a QUAL='34343' ID='3' />

so i have two columns in XML instead of one
Thanks
Nouman

 

[boflexson]

nomi2000, Here is the split function I use: in the next post is the script to then do your query.
--------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Split]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO





CREATE FUNCTION Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END

END

RETURN
END





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




-Adam T. Courtney

 

[boflexson]

SELECT '<a QUAL=''' + QUAL.Value + ''' ID=''' + IDV.Value + ''' />' FROM (
SELECT * FROM dbo.Split('34343,555,22',',')) AS QUAL
JOIN (SELECT * FROM dbo.Split('1,2,3',',')) AS IDV ON QUAL.IDX = IDV.IDX

--Hope this helps

-Adam T. Courtney

 

[nomi2000]

Hi Adam
Its a good function ,perhaps gr8 function i haven't tested it but it looks good to me
thanks again
Nouman