CODEWITHSUNDEEP
sqlsql-server
Gray Meiring
Gray Meiring

Reputation: 421

Split String With Commas in SQL

I am trying to create a dynamic query that will allow me to have multiple values in once Variable. To get this right I have been working on the following query:

DECLARE 
@Variables VARCHAR(MAX) = 'MOVE ON UP,WILD BREAK,PRETWIST',
@Result VARCHAR(MAX)

SET @Result = (SELECT REPLACE(@Variables, ',', '') )
SELECT 
S = STUFF((
SELECT ',' +S
FROM(
        SELECT S= '''' + SUBSTRING(@Result, N, 1) + ''''
         FROM (SELECT TOP (LEN(@Result)) N= ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master..spt_values ) N
    ) B1
FOR XML PATH ('')),1,1,'')

My query gives the output as:

'M','O','V','E',' ','O','N',' ','U','P','W','I','L','D',' ','B','R','E','A','K','P','R','E','T','W','I','S','T'

Where as my desired output is:

'MOVE ON UP','WILD BREAK','PRETWIST'

Please could someone assist me and show me where I am going wrong?

Upvotes: 1

Views: 491

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

Here is a 2008+ compliant approach which does not require macro substitution nor dynamic SQL

Example

DECLARE @Variables VARCHAR(MAX) = 'MOVE ON UP,WILD BREAK,PRETWIST'

Select A.*
 From  YourTable A
 Where [SomeCol] in (
                    Select Value = B.i.value('(./text())[1]', 'varchar(max)')
                    From  (Select x = Cast('<x>' + replace(@Variables,',','</x><x>')+'</x>' as xml)) as A 
                    Cross Apply x.nodes('x') AS B(i)
                  )

Or as a JOIN

DECLARE @Variables VARCHAR(MAX) = 'MOVE ON UP,WILD BREAK,PRETWIST'

Select A.*
 From  YourTable A
 Join  (
        Select Value = B.i.value('(./text())[1]', 'varchar(max)')
        From  (Select x = Cast('<x>' + replace(@Variables,',','</x><x>')+'</x>' as xml)) as A 
        Cross Apply x.nodes('x') AS B(i)
       ) B
 on A.StateCode=B.Value

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

Not 100% clear on your intent, however ...

Example

DECLARE @Variables VARCHAR(MAX) = 'MOVE ON UP,WILD BREAK,PRETWIST'

Select ''''+replace(@Variables,',',''',''')+''''

Returns

'MOVE ON UP','WILD BREAK','PRETWIST'

Upvotes: 2

Alex Sham
Alex Sham

Reputation: 457

You can use STRING_SPLIT() function:

Select
Value
From STRING_SPLIT(@Variables, ',');

Which will return you rows with your words https://learn.microsoft.com/ru-ru/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

And then gather it as you wish with xml path or string_agg as you wish

Upvotes: 1

Related Questions