April 28, 2006 at 2:04 am
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
April 28, 2006 at 4:21 pm
Just curious, what will you be using this table for? It seems like you are introducing an awful lot of overhead here. How many users does this system have? Creating and populating a table each time a user logs into the system could be very resource, especially I/O, intensive.
May 2, 2006 at 1:56 am
This is just the way the table has been designed initially. We are going to be re-writing this. However, in the meantime, does anyone have a "step by step" solution.
Someone gave me this code:
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
Does this help at all?
May 2, 2006 at 1:57 am
Could you walk me through this?
Can I turn this into a stored procedure? If so, what do I need to add to it so that it will run?
Thanks,
Andy
May 3, 2006 at 10:33 am
This function accepts a character string and, based off of the delimiter that you pass in, it will split the string down into its individual parts. Those parts are then placed into a table variable and the table variable is passed back to the function call. Consider the following example, lets say you have your Original table as posted in your first post. In order to use this function as-is and populate a new table upon user logon, you would need to first select the correct row from your original table and assign that value to a user defined variable. You could then use a SELECT INTO to create your new table from the results returned in the table variable from the function. The function could be converted to a stored procedure or you could leave the function alone and place the function call into a stored procedure.
DECLARE @List varchar(4000)
SET @List = (SELECT List FROM OrigTable WHERE RowID = 1)
SELECT fnSplit.Items INTO NewTable
FROM dbo.Split(@List,'|') as fnSplit
May 3, 2006 at 3:08 pm
Thanks John,
I implemented this today (after trimming it down quite a bit) and it worked.
Thanks for the reply - makes life a little easier...
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy