CODEWITHSUNDEEP
sql-serverregexescaping
redrubia
redrubia

Reputation: 2366

Escaping Strings in SQL Server

Am having issues with escaping parts of strings in SQL. On example is:

SELECT TOP 10000 *
FROM experience
WHERE name IS
'AT&T'

Which is saying incorrect syntax near Incorrect syntax near 'AT'. Seems its an issue with the & - is there any general rule to escaping?

Have also tried 

SELECT TOP 10000 *
FROM experience
WHERE name Like
'AT&\T'

This works, although gives no results (there are results which should come up)

Upvotes: 1

Views: 1419

Answers (1)

Martin Smith
Martin Smith

Reputation: 453152

The only character that needs escaping in a string literal is the single quote. '. This is escaped by doubling them up instead of with a backslash.

SELECT 'O''Reilly'

In the vast majority of cases you should be using parameterised queries anyway and never need to even do that.

The correct operator to use is =

SELECT TOP 10000 *
FROM experience
WHERE name = 'AT&T'

Works Fine SQL Fiddle Demo

IS is only used in conjunction with [NOT] NULL

The only special significance backslash has in a string literal is if immediately before a line break when it acts as a line continuation character.

PRINT 'This is all \
one line'

Returns

This is all one line

Upvotes: 2

Related Questions