CODEWITHSUNDEEP
sqlapostrophe
Rivka
Rivka

Reputation: 2202

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.

Upvotes: 0

Views: 1418

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112382

You don't need to concatenate strings.

SELECT @COL1, @COL2

should be sufficient. The parameters @COL1 and @COL2 will be replaced by the actual values automatically.

However you cannot declare column names dynamically like this. The parameters on stand for values. Usually you would do something like this:

SELECT [Name] FROM mytable WHERE ID=@id

If you intend to change the column names dynamically, then you would not use apostrophes at all:

SET @sql = 'SELECT [' + @COL1 + '], [' + @COL2 + ' FROM mytable';
EXECUTE sp_executesql @sql;

Upvotes: 0

BRPocock
BRPocock

Reputation: 13914

Well, the quick answer is: '''' (double them up)

e.g. SELECT 'XX''''XX' → XX''XX

I'll leave it at that, because the "why would you want to do that‽" part makes me very nervous.

Upvotes: 1

Tarwn
Tarwn

Reputation: 1030

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+ ']'

Upvotes: 3

Related Questions