How to select values from string separated by comma in SQL Query?

Question

I want to select values from string separated by comma (,) in SQL Query, I tried with the following query but it gives only 3 columns and I want like 8 columns

SELECT 
  LEFT(DB_field, CHARINDEX(',', DB_field)-1) as '1st column', 
  SUBSTRING(DB_field, CHARINDEX(',', DB_field)+1, LEN(DB_field)-CHARINDEX(',', DB_field)-CHARINDEX(',',REVERSE(DB_field)))  as '2nd column',
  RIGHT(DB_field, CHARINDEX(',', REVERSE(DB_field))-1)   as '3rd column'
FROM Table_Name

DB_field has string value but separate by comma (,) something like 123,456,789,abcd,efghjk,lmno,pqrs,tuvw How can I select that values from that string in SQL Query?

Please anyone can help me

Possible duplicate of [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — Valerica, Nov 10 '17 at 09:13
This question has been asked many, many, many times before. Please use your favorite search engine. — HoneyBadger, Nov 10 '17 at 09:15

Jibin Balachandran · Answer 1 · 2017-11-10T09:20:16.587

Create a custom function if you are using SQL Server 2014 or less.

CREATE FUNCTION SplitString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

And use the above function in your select query.

SELECT Item
FROM dbo.SplitString('Apple,Mango,Banana,Guava')

You can refer this for more info

If you are using SQL Server 2016, then you can use STRING_SPLIT

SELECT value  
FROM STRING_SPLIT('clothing,road,,touring,bike', ',')  
WHERE RTRIM(value) <> '';

Thank you @Jibin I'm using SQL Server 2016, and the solution you provided really helped me. Special thanks — Ermenegildo Cumbe, Aug 31 '22 at 07:59

score 1 · Accepted Answer · answered Nov 10 '17 at 09:50

XML-based solution:

;WITH cte AS (
    SELECT *
    FROM (VALUES
    (N'123,456,789,abcd,efghjk,lmno,pqrs,tuvw'),
    (N'987,654,321,dbca,kjhgfe,onml,srqp,xyz')
    ) as t(DB_field)
)

SELECT  t.c.value('/a[1]','nvarchar(max)') as [1column],
        t.c.value('/a[2]','nvarchar(max)') as [2column],
        t.c.value('/a[3]','nvarchar(max)') as [3column],
        t.c.value('/a[4]','nvarchar(max)') as [4column],
        t.c.value('/a[5]','nvarchar(max)') as [5column],
        t.c.value('/a[6]','nvarchar(max)') as [6column],
        t.c.value('/a[7]','nvarchar(max)') as [7column],
        t.c.value('/a[8]','nvarchar(max)') as [8column],
        t.c.value('/a[9]','nvarchar(max)') as [9column]
FROM (
    SELECT CAST(('<a>'+REPLACE(DB_field,',','</a><a>') +'</a>') as xml) as x
    FROM cte c
) as xt
CROSS APPLY xt.x.nodes('/') as t(c)

Output:

1column 2column 3column 4column 5column 6column 7column 8column 9column
123     456     789     abcd    efghjk  lmno    pqrs    tuvw    NULL
987     654     321     dbca    kjhgfe  onml    srqp    xyz     NULL

How to select values from string separated by comma in SQL Query?

2 Answers2