asked on

SQL Split() function

Hi, Experts:
Does SQL Server 2000 has any function or extended storeprocedures that has
the same functionality as Split() function in VBScript or Javascript? I try to split the
following string: AD/DC/EF/KU/MS/MT.
I have a table I need to clean up, the table structure looks like this:

ID LocationCode JAN FEB MAR APR MAY
1 AD/DC/EF/KU/MS/MT X
2 ED/LC/JF/KU/MT X
3 KD/DM/EF/ X

I want the data in this format:

ID LocationCoe Mon
3 KD 3 (MAR)
3 DM 3 (MAR)
3 EF 3 (MAR)

A Split function in UDF would be helpful ^_^

Thanks a lot
CKCAT ^_^

ASKER CERTIFIED SOLUTION

sachiek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sachiek

Another one too.

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 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(Items) VALUES(@SLICE)
      -- 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

Lem_E_Tweakit

create procedure split_string
@i varchar(100),@ii varchar(100)
as

declare @j int
declare @n int
declare @m int
declare @k int
select @n=1
select @k=0

select @j=len(@i)

while @n<=@j
begin
select @m=CHARINDEX ( @ii,@i,@n)

if @m=0 and @k>0
begin
select @m=@j+1
end

print ltrim(substring(@i,@n,@m-@k-1))
select @k=@m
select @n=@m+1

end

call it with exec split_string 'KD/DM/EF/','/'
you get:
KD
DM
EF

ckcat

ASKER

Thank you all ^_^