TSQL String modify

Question

I have a problem with string. I have a variable @Rights which looks like 'ASD,ZXC,QWE,IOP,JKL' What I need to do is use this string in

SELECT * FROM dbo.Example
WHERE Rights IN (@Rights)

Problem is that I need to convert:

'ASD,ZXC,QWE,IOP,JKL'

to:

'ASD','ZXC','QWE','IOP','JKL'

How can I do this?

If `@Rights` is a `varchar` variable then it can only store strings. You to either use a `table variable` or pass `@Rights` to a string split function. — Giorgos Betsos, Oct 08 '15 at 08:38
Possible duplicate of [Turning a Comma Separated string into individual rows](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) — Bridge, Oct 08 '15 at 08:39

score 1 · Accepted Answer · answered Oct 08 '15 at 08:43

you can create a split function

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

and after you can convert the string in a temp table

DECLARE @Rights NVARCHAR(128)
SET @Rights = 'ASD,ZXC,QWE,IOP,JKL'
SELECT *
INTO #Temp
FROM dbo.Split(@Rights , ',')

and after you can use it in your query like this

SELECT * FROM dbo.Example
WHERE Rights IN (SELECT Data FROM #Temp)

score 1 · Answer 2 · answered Oct 08 '15 at 08:44

1

You can try like this:

DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)
SET @str = 'ASD,ZXC,QWE,IOP,JKL'
SET @delimiter = ','
SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml)
SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)

SQL DEMO

answered Oct 08 '15 at 08:44

Rahul Tripathi

168,305
31
280
331

Thx for answer, it's good solution but I will use a Mattia Caputo hint. – Piotr Sowiak Oct 08 '15 at 09:20

score 0 · Answer 3 · answered Oct 08 '15 at 08:41

You can use Dynamic SQL:

LiveDemo

-- sample data
CREATE TABLE #Example(ID INT IDENTITY(1,1), Rights VARCHAR(100));
INSERT INTO #Example VALUES ('ASD'), ('ABC'), ('IOP');


DECLARE @Rights NVARCHAR(MAX) = 'ASD,ZXC,QWE,IOP,JKL';
DECLARE @sql NVARCHAR(MAX) = 
  N'SELECT * 
   FROM #Example
   WHERE Rights IN (''<placeholder>'')';

SET @sql = REPLACE(@sql, '<placeholder>', REPLACE(@Rights, ',', ''','''));

-- SELECT @sql; -- for debug

EXEC dbo.sp_executesql
    @sql;

But you should reconsider using Table Valued Parameter instead.

TSQL String modify

3 Answers3

Linked

Related