Technical Article

spilt function

,

Many a times we need to split a string into its indivisual words and return a array like the Split function of Visal Basic which accepts a delimeter. Here is a script which assumes the delimeter to be the space character and works in a similar method.

Declare @sTemp varchar(8000),
@sTempValue varchar(1000),
@sIgnoreChar varchar(10),
@sIgnoreWord varchar(8000),
@iPos int,
@sValue varchar(8000)

DECLARE @tbSplittedValues TABLE(SplittedValue varchar(1000))

SELECT @sValue = '1        2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21'
SELECT @sTemp = @sValue 

SELECT @iPos = CHARINDEX(' ', @sTemp)
WHILE @iPos <> 0
BEGIN
SELECT @sTempValue = SUBSTRING(@sTemp, 1, @iPos-1)
IF LEN(@sTempValue) > 0
BEGIN
INSERT @tbSplittedValues(SplittedValue)
SELECT @sTempValue
END
SELECT @sTemp = SUBSTRING(@sTemp, @iPos + 1, Len(@sTemp))
SELECT @iPos = CHARINDEX(' ', @sTemp)
END 
IF @iPos = 0
BEGIN
IF LEN(@sTemp) > 0
BEGIN
INSERT @tbSplittedValues(SplittedValue)
VALUES(@sTemp)
END
END
SELECT * FROM @tbSplittedValues

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating