How to split one column into multiple rows in SQL?

Question

I have a table like this.

id  items
1  1|2|3
2  3|4
...

Now, I want to split items to multiple rows, like this.

id  item
1  1
1  2
1  3
2  3
2  4
...

Can SQL do this job?

If @GordonLinoff were here, he would tell you that the _best_ thing to do is to normalize your database structure so that items are stored in a relational manner. Right now, if you hand me an `id`, I have no way of knowing with _which_ item it is associated. — Tim Biegeleisen, Jul 02 '15 at 08:12
Never store data as separated items like that, it will only cause you lots of problems. One item per row is the SQL way! — jarlh, Jul 02 '15 at 08:14

score -1 · Answer 1 · answered Jul 02 '15 at 08:18

You shouldn't store data in this format in your database due to index usages etc. But in some cases, you can't avoid it, if an foreign application delivers information in this way for example. If you have to store it this way and disaggregate it to store it in a good format you can use the following user defined function to achieve it.

CREATE FUNCTION dbo.udf_split (@String nvarchar(max), @Delimiter nchar(1))

RETURNS @Results Table (Items nvarchar(max))
AS
BEGIN
    DECLARE @Index int
    DECLARE @Slice nvarchar(max)

    SET @Index = 1

    IF @String IS NULL RETURN

    WHILE @Index != 0
    BEGIN
        SELECT @Index = CHARINDEX(@Delimiter, @String)

        IF @Index != 0
            SELECT @Slice = LEFT(@String, @Index - 1)
        ELSE
            SELECT @Slice = @String

            INSERT INTO @Results(Items) VALUES (LTRIM(RTRIM(@Slice)))
                SELECT @String = RIGHT(@String, LEN(@String) - @Index)

            IF Len(@String) = 0 BREAK
        END
    RETURN
END
GO

How to split one column into multiple rows in SQL?

1 Answers1