How to escape back slash in SQL server

问题:

DECLARE @Query nvarchar(max)
SET @Query ='DECLARE @Test nvarchar(max)
SELECT @Test = ''\a'\b'\c''
SELECT @Test
PRINT @Query
exec sp_executesql @Query

I am trying to get an output as \a\b\c. The above error is probably because I am not able to escape the \ character.

回答1:

You do not need to escape the backslashes (only the inner single quotes):

DECLARE @Query nvarchar(max)
SET @Query ='DECLARE @Test nvarchar(max)
SELECT @Test = ''\a\b\c''
SELECT @Test'
PRINT @Query
exec sp_executesql @Query

回答2:

There is in fact one place where back slashes do need to be escaped (or at least treated specially) in SQL Server.

When a backslash immediately precedes a new line in a string literal both the backslash and the new line are removed.

PRINT 'Foo\
Bar'

Returns

FooBar

The article here indicates that this is a feature of the client tools rather than TSQL itself but I don't believe this to be the case as the following code has the same result

DECLARE @X VARCHAR(100) = 'PRINT ''Foo\' + CHAR(13) + CHAR(10) + 'Bar'' '
EXEC(@X)

If you actually require a string literal with a backslash followed by carriage returns you can double them up.

PRINT 'Foo\\

Bar'

回答3:

If you want to test try this code:

select '\a\b\c'

There is no necessity to escape the backslash character. But the issue of backslash plus newline character is a problem and Sql Server engine simply cut it out.

If you need to put a single quotation mark on the string, then you need to double the quotation mark character:

select 'test: ''in quotes'' - ok'

Hope I'd helped in some way.