Need to take a delimited string and output to new table

  • 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

     

  • 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. 

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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?

  • 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

  • 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

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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