Break string into SQL column regarding a specific character

  • Hi all,

    I want to separate string within a table column, accordint to a specific character.

    eg.

    Field Value: test1,test2,test3,test4

    Expected Result (break by character ","):

    New Field

    test1

    test2

    test3

    test4

    Any help?

     

  • explanation: I don't know the number of delimiter characters in each row (in my example, the character "," may have different number of occurences in each row).

  • also, in each occurence of the character, I need a counter which will increase and show me for each record of the resultset which is the occurence sequense.

  • Take a look at the delimitedsplit functions, do these do what you need.

    https://www.sqlservercentral.com/forums/topic/using-the-delimitedsplit8k-function-help-jeff

  • DROP TABLE IF EXISTS #ToSplit;

    CREATE TABLE #ToSplit
    (
    SplitCol VARCHAR(8000) NOT NULL
    );

    INSERT #ToSplit
    (
    SplitCol
    )
    VALUES
    ('test1,test2,test3,test4')
    ,('test5,test6');

    SELECT ts.SplitCol
    ,ss.value
    FROM #ToSplit ts
    CROSS APPLY STRING_SPLIT(ts.SplitCol, ',') ss;

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

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