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;

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.', ',');

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'