Format String into Multiple Values - Separate by Comma - SQL Server

Question

I've this values in a column value named [T] (varchar(100)):

AS00516003,QD00516009,XD00516089

How can I get the following string in order to get the results into IN statement:

'AS00516003','QD00516009','XD00516089'

Because If I do this query with the first value I don't get any values:

  SELECT *
  FROM [dbo].[TABLE]
  WHERE [NUMBER] IN (SELECT  [T]  FROM [dbo].[TEST])

Possible duplicate of [How do I split a string so I can access item x?](https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — Pred, Apr 03 '18 at 09:51

score 0 · Answer 1 · answered Apr 03 '18 at 09:53

0

You might use String_Split, like this:

SELECT [T]
FROM YourTable
CROSS APPLY STRING_SPLIT(Tags, ',');

answered Apr 03 '18 at 09:53

SQL_M

2,455
2
16
30

score 0 · Accepted Answer · answered Apr 03 '18 at 09:59

You need dynamic SQL in order to achieve that:

declare @query varchar(4000)
set @query = 'select * from [dbo].[table] where [number] in ('
--here you will append results from [T] table, all values stored in this table
--will be in the list in WHERE clause of dynamic query
select @query += '''' + REPLACE([T], ',', ''',''') + ''',' from [dbo].[TEST]
--remove last comma and close list with )
set @query = LEFT(@query, len(@query) - 1) + ')'

exec(@query)

score 0 · Answer 3 · answered Apr 03 '18 at 10:24

Try this below script

DECLARE @val nvarchar(max)='AS00516003,QD00516009,XD00516089'
,@ReqFormat nvarchar(max)

DECLARE @temp TABLE (Data nvarchar(100))
INSERT INTO @temp
SELECT @val
SELECT * FROM @temp

;WITH CTE
AS
(
SELECT ROW_NUMBER()OVER(ORDER BY (SELECT 1 )) AS Id
    ,''''+Split.a.value('.','nvarchar(100)')+'''' AS Data
FROM
(
SELECT  CAST('<S>'+REPLACE(Data,',','</S><S>')+'</S>' AS XML)Data from @temp
)AS A
CROSS APPLY Data.nodes('S') AS Split(a)
)
SELECT @ReqFormat=STUFF((SELECT ', '+Data  FROM CTE
FOR XML PATH ('')),1,1,'')

SELECT @ReqFormat AS ReqFormat

Result

GivenData
-----------------------------------
AS00516003,QD00516009,XD00516089

ReqFormat
----------------------------------------
'AS00516003', 'QD00516009', 'XD00516089'

score 0 · Answer 4 · answered Apr 03 '18 at 10:34

You can make script as user defined function like below

CREATE FUNCTION [dbo].[udf_GetUnsplitData]
(
@string nvarchar(max)
)
RETURNS  @OutTable TABLE
(
DATA nvarchar(max)
)
AS
BEGIN

    DECLARE @Temp AS TABLE
    (
    DATA nvarchar(max)
    )
    INSERT INTO @Temp
    SELECT @string

    DECLARE @OutTableInternal AS TABLE
    (
    DATA nvarchar(max)
    )

    INSERT INTO @OutTableInternal(DATA)
    SELECT 
     ''''+Split.a.value('.','nvarchar(1000)')+'''' As DATA
    FROM
    (
    SELECT 
    CAST('<S>'+REPLACE(DATA,',','</S><S>')+'</S>' AS XML ) AS DATA
    FROM @Temp
    )A
    CROSS APPLY DATA.nodes('S') AS Split(a)

    INSERT INTO @OutTable(DATA)
    SELECT STUFF((SELECT ', '+DATA FROM @OutTableInternal
    FOR XML PATH ('')),1,1,'')


RETURN
END

SELECT * FROM [dbo].[udf_GetUnsplitData] (
   'AS00516003,QD00516009,XD00516089')
GO

Result

DATA
---------------------------------------
'AS00516003', 'QD00516009', 'XD00516089'

Format String into Multiple Values - Separate by Comma - SQL Server

4 Answers4