Search code examples
sqlapostrophe

SQL Apostrophes

How can I generate a string in SQL which contains an empty string: ' '

I tried this:

DECLARE @TEST NVARCHAR(50), @COL1 NVARCHAR(50), @COL2 NVARCHAR(50) SELECT @COL1 = 'A', @COL2 = 'B'

SELECT @TEST = 'SELECT '' ['+ @COL1 + '], ''[' + @COL2+ ']'

SELECT @TEST

But the string ends up looking like:

SELECT ' [A], '[B]

When it needs to look like:

SELECT '' [A], ''[B]

Thanks.

Solution

  • Two single quotes in a string in SQL is treated as a single escaped single quote, so in order to generate two in the output, you need to put 4 in the input, like so:

    SELECT @TEST = 'SELECT '''' ['+ @COL1 + '], '''' [' + @COL2+ ']'