CODEWITHSUNDEEP
sqlsql-server
Đỗ Mạnh Hùng Đỗ Mạnh Hùng
Đỗ Mạnh Hùng Đỗ Mạnh Hùng

Reputation: 21

Splitting a space-separated list of values in SQL server

I have a table containing an ID and Value, where the Value field is a space separated list of values:

ID           Value   
100           A1 A2 A3     
200           B1           
300           C1 C2

I want to split the values string and have each one as a separate row with the ID:

ID          Value
100           A1           
100           A2          
100           A3           
200           B1           
300           C1          
300           C2

How can I do this in T-SQL?

Upvotes: 2

Views: 11066

Answers (3)

Diwas Poudel
Diwas Poudel

Reputation: 847

SQL Server 2016 and above has STRING_SPLIT() function to split a string into a row of substrings based on a specified separator. So, for your problem you can simply do this:

SELECT  ID,s.value FROM dbo.TableName t
outer apply STRING_SPLIT(t.Value, ' ') s

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try:

SELECT 
     ID, 
    Split.a.value('.', 'VARCHAR(100)') AS [Value]
FROM  
(
     SELECT
        ID,
        CAST ('<M>' + REPLACE(Value, ' ', '</M><M>') + '</M>' AS XML) AS CVS  
    FROM YourTable
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)

SQL Fiddle Demo

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460068

You can use a custom Split function and CROSS APPLY:

SELECT ID, Split.Item As Value
FROM dbo.TableName
CROSS APPLY dbo.Split(Value, ' ')as Split

DEMO

But it's better to normalize your database and use a separate table to link the ID with the Values.

I have used this table-valued-function:

CREATE FUNCTION [dbo].[Split]
(
    @ItemList NVARCHAR(MAX), 
    @delimiter CHAR(1)
)
RETURNS @ItemTable TABLE (Item VARCHAR(250))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(MAX)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    --SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @ItemTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END

Upvotes: 4

Related Questions