.Net ramblings
# Friday, 09 May 2008
SQL: String Split Function
I don't know why this isn't part of the built-in functions, especially in sql 2005 but anyway, here it is thanks to this groups post:
CREATE FUNCTION Split(@String varchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int, Items nvarchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @ID int

SELECT @INDEX = 1, @ID = 1
WHILE @INDEX !=0

BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
SELECT @ID = @ID + 1
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
Then you can do something like this:
select Items from dbo.Split(@List, ',')


Friday, 09 May 2008 12:40:24 (GMT Daylight Time, UTC+01:00)  #    Comments [6]  Database

Tuesday, 09 December 2008 12:52:26 (GMT Standard Time, UTC+00:00)
Great stuff, Tim; this was just what I was looking for. Thanks a lot!
Ed Graham
Thursday, 30 April 2009 17:25:15 (GMT Daylight Time, UTC+01:00)
i found a same errors

i 'm sending true code:

Create FUNCTION Split(@String varchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int, Items nvarchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @ID int

SELECT @INDEX = 1, @ID = 1
WHILE @INDEX !=0

BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@String)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@String,@INDEX - 1)
ELSE
SELECT @SLICE = @String
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
SELECT @ID = @ID + 1
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @String = RIGHT(@String,LEN(@String) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@String) = 0 BREAK
END
Return
END

cesur
Monday, 13 July 2009 17:43:45 (GMT Daylight Time, UTC+01:00)
Thanks, Tim. This is simple and direct. However, you could make it slightly shorter by declaring @Results TABLE (ID int identity, ...

and then remove all the @ID stuff.
Kipb7
Monday, 13 July 2009 17:46:50 (GMT Daylight Time, UTC+01:00)
hi kipb7
thanks for the tip
tim
Thursday, 28 January 2010 01:03:09 (GMT Standard Time, UTC+00:00)
Hi,

This function helped me a lot, but I have the following string in a field:


DP1201|DP1210|DP1220|DP1230|DP1240|DP1500|DP1510|DP1520|DP1530|DP2301|DP2310..DP2320|DP2340|DP2700|P2710|DP6010|DP6020|DP7000..DP7999

I would like to be able to do the following type of query against these values:

select DEPARTMENT from DIMENSION where DEPARTMENT in (select dimfilter from userfilter) or DEPARTMENT between (select case when value2<> ' ' then value1 end ) and (select case when value2<> ' ' then value2)

the issue is that there are two delimiters, '|' and '..'

the '|' delimits single values, the '..' is for from .. to.

How can I create a second column with the second value after the .. (DP2310..DP2320 or DP7000..DP7999) in a second column?

longinthetooth
Thursday, 28 January 2010 13:51:38 (GMT Standard Time, UTC+00:00)
hi longinthetooth
you could try modifying the code to accept 2 delimiter parameters, and then scan for either delimiter in the CHARINDEX calls etc.
good luck
tim
tim
OpenID
Please login with either your OpenID above, or your details below.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview