I have a stored procedure where I will use @variable in
WHERE colname LIKE(@variable)
This @variable could be a string list representing column names, such as
EXEC usp_cols 'col1, col2, col3';
I want to split this string to list of strings in the stored procedure, so that it converts to 'col1', 'col2', 'col3' in the stored procedure to use it in the LIKE statement:
WHERE colname LIKE('col1', 'col2', 'col3')
As mention in comment, from 2016 you can use STRING_SPLIT. Read more about it here: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16
It is as simple to use as this:
SELECT value FROM STRING_SPLIT('Lorem,ipsum,dolor,sit,amet.', ',');
Could you please try
DECLARE @VARIABLE VARCHAR(100);
SET @VARIABLE='COL1,COL2,COL3';
DECLARE @DELIMITER CHAR(1)=',';
SELECT VALUE FROM STRING_SPLIT(@VARIABLE,@DELIMITER);
--EXAMPLE
IF OBJECT_ID(N'tempdb..#T',N'U')IS NOT NULL
DROP TABLE tempdb..#T;
CREATE TABLE #T
(
ID TINYINT NOT NULL,
SURNAME VARCHAR(100)
)
INSERT #T(ID,SURNAME)VALUES(1,'IVAN'),(2,'JOHN'),(3,'PETER'),(4,'PAUL')
DECLARE @InSearchValues VARCHAR(100);
DECLARE @InDelimeter CHAR(1);
SET @InDelimeter=',';
SET @InSearchValues='PETER,PAUL,IV%';
SELECT T.ID,T.SURNAME
FROM #T AS T
CROSS APPLY
(
SELECT X.value FROM string_split(@InSearchValues,@InDelimeter)AS X
WHERE T.SURNAME LIKE X.value
)R
DROP TABLE #T;
WHERE colname LIKE to answer the question.
Simplest version.
SQL
DECLARE @input VARCHAR(100) = 'COL1 , COL2 , COL3'
, @separator CHAR(2) = ', '
, @quote CHAR(1) = CHAR(39)
, @output VARCHAR(500);
-- Method #1
SET @output = @quote + REPLACE(@input, @separator, CONCAT(@quote,',', @quote)) + @quote;
SELECT @output;
/*
Separator could be ',' or ', ' (with space after the coma), ' , ' (or space before and after)
*/
-- Method #2, SQL Server 2017 onwards
SELECT @output = STRING_AGG(CONCAT(@quote, TRIM(value), @quote), ',')
FROM STRING_SPLIT(@input, ',');
SELECT @output;
With a little help from the community, I think I have the result that I wanted:
DECLARE @VARIABLE VARCHAR(100);
SET @VARIABLE='COL1, COL2, COL3';
DECLARE @DELIMITER CHAR(1)=',';
DECLARE @variable2 varchar(500)
SELECT VALUE
INTO #columnNames
FROM STRING_SPLIT(@VARIABLE,@DELIMITER)
SELECT@variable2 = coalesce(@variable2 + ',', '') + convert(varchar(500), '''' + LTRIM(RTRIM(value)) + '''') from #columnNames
PRINT@variable2
Now I get the following result which can be implemented in WHERE... LIKE clause:
'COL1','COL2','COL3'
@variable2 do in this case? You would only have a single result from the temp tableSTRING_AGG instead.
STRING_SPLIT.