Tim Mackey's Weblog

| |

Search

Blogroll

Friday, May 09, 2008

Friday, May 09, 2008 12:40:24 PM (GMT Daylight Time, UTC+01:00) ( Database )

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, ',')

Comments [6] | |

Tuesday, December 09, 2008 12:52:26 PM (GMT Standard Time, UTC+00:00)

Great stuff, Tim; this was just what I was looking for. Thanks a lot!

Ed Graham

Thursday, April 30, 2009 5:25:15 PM (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, July 13, 2009 5:43:45 PM (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, July 13, 2009 5:46:50 PM (GMT Daylight Time, UTC+01:00)

hi kipb7
thanks for the tip

tim

Thursday, January 28, 2010 1:03:09 AM (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, January 28, 2010 1:51:38 PM (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

Name
E-mail
Home page

	Remember Me
Comment (HTML not allowed)
Enter the code shown (prevents robots):
Live Comment Preview

mostly.Net

Search

Categories

Archive

Blogroll