Need to take a delimited string and output to new table

[andy1704]

Hi guys,

I've a question for you:

I've a table with (lets say 10 rows). One of these rows contains a list of numbers separated by a | - for example:

134|454|5664|554|245|4663
9344|6523|6664|554|295|432
2334|4240|3948|894|745|11
3434|24|564|9954|4345|13

What I need to do is take these individual values out of the record and create a new table with them so that the table would look like this:

Field
--------
134
454
5664
554
245
4663
9344
6523
6664
554
295
432
2334
4240
3948
894
745
11
3434
24
564
9954
4345
13

I need to create this table evry time a user logs in as it will be updated in the first table.

Any help would be very much appreciated.

Thanks,

Andy

 

[sweth]

Ok, you can use split table function function , not inbuit
which looks like
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
-- following line added 10/06/04 as null
-- values cause issues
IF @String IS NULL RETURN
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

select * from dbo.split('1212|2122121|78788|87887','|')
will give u
1212
2122121
78788
87887

Hope this will useful to you

Thanks
Siva