How to split a comma delimited string using ONLY a select statement?

Question

Here's the situation:

I cannot implement a custom function. PLEASE let's not get into a debate about why. That's just the way the sitation is and I cannot change that
I would need to somehow split the comma delimited values up using a SELECT statement of some kind
I cannot use the built in STRING_SPLIT function because I'd need to set the database compatibly to 130, which I cannot do, due to permission issue

So with that all mentioned, how can I spit up something like 'This,Is,A,Sentence' using a select statement?

I am using SQL Server 2008.

Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — Morgan Thrapp, Aug 21 '18 at 22:53
There's at least one answer on that dupe that shows how to do it with a CTE. If you can't use either a CTE or a temporary function for the transaction, you're out of luck — Morgan Thrapp, Aug 21 '18 at 22:54
@morgan-thrapp, nah, you don't have to use a CTE, proc, or UDF. See my answer below. — digital.aaron, Aug 21 '18 at 22:55
@digital.aaron I mean, a temp table and a CTE aren't really that different. — Morgan Thrapp, Aug 21 '18 at 22:56
Why do you let yourself get into this situation in the first place? SQL Server has types *designed* for holding multiple values (tables, xml), and yet you're letting someone give you a string, when T-SQL's string manipulation functionality is notoriously weak. — Damien_The_Unbeliever, Aug 22 '18 at 09:13
The answer was given to you [weeks ago](https://stackoverflow.com/questions/45991269/split-string-via-select-statement) when you asked the same question (but with less information). — SMor, Aug 22 '18 at 11:36

score 2 · Accepted Answer · answered Aug 21 '18 at 22:56

Query

 Declare @String nvarchar(500) = 'This,Is,A,Sentence';

SELECT  Split.a.value('.', 'VARCHAR(100)') Words
FROM (
      SELECT Cast ('<X>' 
                   + Replace(@String, ',', '</X><X>') 
                   + '</X>' AS XML) AS Data
     ) AS t CROSS APPLY Data.nodes ('/X') AS Split(a);

Result Set:

╔══════════╗
║  Words   ║
╠══════════╣
║ This     ║
║ Is       ║
║ A        ║
║ Sentence ║
╚══════════╝

Matthew Baker · Answer 2 · 2018-08-22T09:08:20.860

A plus 1 to the guys suggesting cte's. You're probably going to find that their suggestions are going to be the best option. The code I use for this is usually wrapped in a user defined function, but I've stripped it out for an example here:

DECLARE @str       NVARCHAR(MAX) = 'This,Is,A,Sentence'
,       @Delimiter NCHAR(1)      = ',';

WITH cte AS
    (SELECT 1                                                  AS ID
     ,      CASE WHEN CHARINDEX(@Delimiter, @str, 1) = 0 THEN @str
                 ELSE LEFT(@str, CHARINDEX(@Delimiter, @str, 1) - 1)
            END  Words
     ,      CASE WHEN CHARINDEX(@Delimiter, @str, 1) = 0 THEN ''
                 ELSE STUFF(@str, 1, CHARINDEX(@Delimiter, @str, 1), '')
            END  Remainder
     UNION ALL
     SELECT cte.ID + 1
     ,      CASE WHEN CHARINDEX(@Delimiter, cte.Remainder, 1) = 0 THEN cte.Remainder
                 ELSE LEFT(cte.Remainder, CHARINDEX(@Delimiter, cte.Remainder, 1) - 1)
            END
     ,      CASE WHEN CHARINDEX(@Delimiter, cte.Remainder, 1) = 0 THEN ''
                 ELSE STUFF(cte.Remainder, 1, CHARINDEX(@Delimiter, cte.Remainder, 1), '')
            END  Remainder
       FROM cte
       WHERE Remainder <> '')
SELECT  cte.ID [Index]
,       cte.Words
  FROM  cte;

Result set:

You can of course strip out the id/index column if you're not going to need it

Incidentally, if you compare this to the built in Split function in the latest version of SQL Server, this is actually far more efficient. Running both together cte version 16% of load, built in function 84%. That's a big difference.

score 0 · Answer 3 · answered Aug 21 '18 at 22:54

Well, not a single SELECT, but here's something you can use without calling a proc or UDF:

CREATE TABLE #OutputTable( SValues VARCHAR(100) )

DECLARE @StringInput VARCHAR(MAX)
        ,@StringTemp    VARCHAR(100)

WHILE LEN(@StringInput) > 0
BEGIN
    SET @StringTemp      = LEFT(@StringInput, 
                            ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                            LEN(@StringInput)))
    SET @StringInput = SUBSTRING(@StringInput,
                                    ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                    LEN(@StringInput)) + 1, LEN(@StringInput))

    INSERT INTO #OutputTable ( SValues )
    VALUES ( @StringTemp )
END

#OutputTable will hold each "word" as a record. You can then query it as needed.

score 0 · Answer 4 · answered Aug 21 '18 at 22:56

If you can use a CTE .....

DECLARE @word VARCHAR(200) = 'This,Is,A,Sentence'

;WITH
a AS (SELECT i=-1, j=0 UNION ALL SELECT j, CHARINDEX(',', @word, j + 1) FROM a WHERE j > i),
b AS (SELECT SUBSTRING(@word, i+1, IIF(j>0, j, LEN(@word)+1)-i-1) word FROM a WHERE i >= 0)
SELECT * FROM b

score 0 · Answer 5 · answered Aug 21 '18 at 23:02

With credit to question: Find nth Occurrence in a string

DECLARE @Val varchar(200) = 'this,is,a,string'

;with t as (
    select @Val as val, 1 as starts, charindex(',', @Val) as pos 
    union all
    select @Val, pos + 1, charindex(',', @Val, pos + 1)
    from t
    where pos > 0
)
select 
    *, substring(@Val, starts, case when pos > 0 then pos - starts else len(@Val) end) token
from T
order by starts

score 0 · Answer 6 · answered Aug 21 '18 at 23:06

This is what I would do:

    DECLARE @t TABLE     
(
ID INT,
Words VARCHAR(500)
)
INSERT @t VALUES (1,'This,Is,A,Sentence')



SELECT 
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Words
FROM
(
SELECT CAST('<XMLRoot><RowData>' + REPLACE(Words,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

Results:

|Words|
 This
 Is
 A
 Sentence

How to split a comma delimited string using ONLY a select statement?

6 Answers6