Split String in Field into new fields Assistance!

  • Hi I need to split data in a field where ; is the delimiter.

    i.e.

    Name

    Joe; Bloggs

    David; Peter; Paul

    Result should be:

    Name                    Name1    Name2    Name3

    Joe; Bloggs            Joe        Bloggs

    David; peter; Paul   David      Peter      Paul

    I am playing and using the charindex function but no luck so far.

    Thanks in advance!!

    p.s I need to get this done by Sat evening!

  • Here is the function I use for this purpose: It requires a numbers table. It will split a string based on any single or multi byte delimiter.

    CREATE FUNCTION dbo.ParseString3 (@String VARCHAR(8000), @Delimiter VARCHAR(10))
    
    RETURNS TABLE
    
    AS
    
    /*******************************************************************************************************
    
    *    ParseString
    
    *
    
    *    Creator:        Robert Cary
    
    *    Date:           9/12/2006
    
    *
    
    *
    
    *    Outline:        Takes a string that is delimited by another string (of one or more characters),
    
    *                    parses it out into tokens and returns the tokens in table format.  Leading
    
    *                    and trailing spaces in each token are removed, and empty tokens are thrown
    
    *                    away.
    
    *
    
    *
    
    *    Usage examples:
    
                    Single-byte delimiter:
    
                         select * from dbo.ParseString3('|HDI|TR|YUM|||', '|')
    
                         select * from dbo.ParseString3('HDI| || TR    |YUM', '|')
    
                         select * from dbo.ParseString3(' HDI| || S P A C E S |YUM | ', '|')
    
                         select * from dbo.ParseString3('HDI|||TR|YUM', '|')
    
                         select * from dbo.ParseString3('', '|')
    
                         select * from dbo.ParseString3('YUM', '|')
    
                         select * from dbo.ParseString3('||||', '|')
    
                         select * from dbo.ParseString3('HDI TR YUM', ' ')
    
                         select * from dbo.ParseString3(' HDI| || S P A C E S |YUM | ', ' ') order by Ident
    
                         select * from dbo.ParseString3(' HDI| || S P A C E S |YUM | ', ' ') order by StringValue
    
    
    
                    Multi-byte delimiter:
    
                         select * from dbo.ParseString3('HDI and TR', 'and')
    
                         select * from dbo.ParseString3('Pebbles and Bamm Bamm', 'and')
    
                         select * from dbo.ParseString3('Pebbles and sandbars', 'and')
    
                         select * from dbo.ParseString3('Pebbles and sandbars', ' and ')
    
                         select * from dbo.ParseString3('Pebbles and sand', 'and')
    
                         select * from dbo.ParseString3('Pebbles and sand', ' and ')
    
    *                   
    
    *
    
    *    Notes:          
    
    *                    
    
    *
    
    *    Modifications
    
    *
    
    *
    
    ********************************************************************************************************/
    
    RETURN (
    
        SELECT IdentStringValue FROM 
    
        (
    
            SELECT ROW_NUMBER() OVER(ORDER BY NumAS Ident,
    
                CASE 
    
                    WHEN DATALENGTH(@delimiterOR @delimiter IS NULL
    
                        THEN SUBSTRING(@stringnum1) 
    
                ELSE
    
                    LTRIM(RTRIM(SUBSTRING(@String,
    
                        CASE 
    
                            WHEN (Num AND SUBSTRING(@String,num ,DATALENGTH(@delimiter)) <> @delimiterTHEN 1
    
                            ELSE Num DATALENGTH(@delimiter)
    
                        END,
    
                        CASE CHARINDEX(@Delimiter@StringNum DATALENGTH(@delimiter))
    
                            WHEN THEN LEN(@String) - Num DATALENGTH(@delimiter)
    
                            ELSE CHARINDEX(@Delimiter@StringNum DATALENGTH(@delimiter)) - Num -
    
                                CASE 
    
    WHEN Num OR (Num AND SUBSTRING(@String,num ,DATALENGTH(@delimiter)) @delimiter) 
    
                                           THEN DATALENGTH(@delimiter)
    
                                    ELSE 0
    
                                END
    
                           END
    
                        ))) 
    
                  END  AS StringValue
    
            FROM dbo.Numbers
    
            WHERE Num <= LEN(@String)
    
                AND (   
    
                        SUBSTRING(@StringNumDATALENGTH(ISNULL(@delimiter,''))) @Delimiter
    
                        OR Num 1
    
                        OR DATALENGTH(ISNULL(@delimiter,'')) 0 
    
                    ) 
    
         ) WHERE DATALENGTH(StringValue) <> 0
    
    )

    SQL guy and Houston Magician

  • Here is some code to build a numbers table.

    CREATE TABLE dbo.Numbers

    (

        Num INT PRIMARY KEY CLUSTERED

    )

    INSERT INTO dbo.Numbers(Num)

    SELECT (i5.[Num] 1000 i4.Num*1000 i3.Num*100 i2.Num*10 i1.NumAS num

    FROM    (SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num]AS i1

            CROSS JOIN

            (SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num]AS i2 

            CROSS JOIN

            (SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num]AS i3 

            CROSS JOIN

            (SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num]AS i4

            CROSS JOIN

            (SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num] UNION ALL

            SELECT AS [Num]AS i5

    WHERE (i5.[Num] 10000 i4.Num*1000 i3.Num*100 i2.Num*10 i1.Num) <> 

    ORDER BY [Num]

    SQL guy and Houston Magician

  • This code isn't quite what you're after, but it'll get you closer. I know you're on a timecrunch, it's late here and I don't have time to put together anything better. In case nobody else suggests anything in time, and you don't figure out something better, here is one approach.

    It is an ugly hack (This function is NOT designed to be used like this and will perform very poorly as it has to hit the numbers table several times for every row in your results)

    but here goes:

    CREATE TABLE #test

    (

        Names   VARCHAR(100)

    )

    INSERT INTO #TEST(Names)

    VALUES('Peter; Paul; Mary')

    INSERT INTO #TEST(Names)

    VALUES('Joe; Bloggs; Company')

    INSERT INTO #TEST(Names)

    VALUES('Really; Really; Really; long; field')

    SELECT Names, (SELECT StringValue FROM dbo.parsestring3(t.Names';'WHERE Ident 1AS Name1,

    (SELECT StringValue FROM dbo.parsestring3(t.Names';'WHERE Ident 2AS Name2

    (SELECT StringValue FROM dbo.parsestring3(t.Names';'WHERE Ident 3AS Name3

    (SELECT StringValue FROM dbo.parsestring3(t.Names';'WHERE Ident 4AS Name4

    (SELECT StringValue FROM dbo.parsestring3(t.Names';'WHERE Ident 5AS Name5,

     (SELECT StringValue FROM dbo.parsestring3(t.Names';'WHERE Ident 6AS Name6,

     (SELECT StringValue FROM dbo.parsestring3(t.Names';'WHERE Ident 7AS Name7,

     (SELECT StringValue FROM dbo.parsestring3(t.Names';'WHERE Ident 8AS Name8,

    (SELECT StringValue FROM dbo.parsestring3(t.Names';'WHERE Ident 9AS Name9,

    (SELECT StringValue FROM dbo.parsestring3(t.Names';'WHERE Ident 10AS Name10

    FROM #test t

    SQL guy and Houston Magician

  • Thanks for all you suggestions guys!!  I've been playing around and nearly there, should be fine for tonight.

    Cheers!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply