Quote
Set @SQl2 = REPLACE( @SQl1, ",", " ',' " );
Danger, Will Robinson!
You will introduce extra spaces
inside the quoted values, creating completely different literals:
REPLACE(@SQl1,","," ',' ");
'415 ',' 417 ',' 418 ',' 419 ',' 420 ',' 416';
^ ^ ^ ^ ^ ^ ^ ^ ^ ^
Now, as to those doubled-up single quotes.
I'm
guessing that your SQL tool is choosing to
display these in their "Copy-n-Paste Safe" format, so that you can use the substituted values directly in other SQL.
You [probably]
don't actually have doubled-up single-quotes in the result; it's just
showing them that way.
If in doubt, try using the value directly in some SQL and see if it works:
SET @SQL1 = '415,417,418,419,420,416' ;
Set @SQL2 = REPLACE(@SQL1,",","','");
Set @SEL = 'select ' || @SQL2 ;
execute @SEL ;
+-----+-----+-----+-----+-----+-----+
| C1 | C2 | C3 | C4 | C5 | C6 |
+-----+-----+-----+-----+-----+-----+
| 415 | 417 | 418 | 419 | 420 | 416 |
+-----+-----+-----+-----+-----+-----+
Regards, Phill W.