Home Forums SQL Server 2008 T-SQL (SS2K8) How to Convert Semi colon Separated Values into Column RE: How to Convert Semi colon Separated Values into Column

  • You could use a recursive cte to split the strings:

    WITH rep AS

    (

    SELECT

    cod

    ,';' delim

    ,name

    from t

    UNION ALL

    SELECT

    LEFT(cod, CHARINDEX(delim, cod, 1) - 1)

    ,delim

    ,LEFT(name, CHARINDEX(delim, name, 1) - 1)

    FROM rep

    WHERE (CHARINDEX(delim, cod, 1) > 0)

    UNION ALL

    SELECT

    RIGHT(cod, LEN(cod) - CHARINDEX(delim, cod, 1))

    ,delim

    ,RIGHT(name, LEN(name) - CHARINDEX(delim, name, 1))

    FROM rep

    WHERE (CHARINDEX(delim, cod, 1) > 0)

    )

    SELECT

    cod

    ,name

    FROM rep

    WHERE (CHARINDEX(delim, cod, 1) = 0)

    AND LEN(cod)>0

    OPTION (MAXRECURSION 0);