Escaping Strings in SQL Server

Question

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)

Also, you may want to look at this answer: http://stackoverflow.com/questions/714980/how-do-you-search-for-a-ampersand-in-the-tsql-contains-function — shree.pat18, Feb 22 '14 at 18:53

Martin Smith · Answer 1 · 2014-02-22T20:09:23.410

2

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'

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

edited Feb 22 '14 at 20:09

answered Feb 22 '14 at 19:41

Martin Smith

This doesn't work - it asks for a substitution variable for 'T' as in the last 'T' after & . Thanks for your advice on operators though! @martinsmith – redrubia Feb 22 '14 at 19:52
@redrubia - What asks for a "substitution variable"? SQL Server certainly doesn't. What tool are you running this in? – Martin Smith Feb 22 '14 at 19:54
@redrubia - I'm guessing that you are using SQL Plus? In which case [see here](http://stackoverflow.com/questions/118190/how-do-i-ignore-ampersands-in-a-sql-script-running-from-sql-plus). Though that would also cast doubt on whether you are actually connecting to SQL Server at all (as opposed to Oracle). And if you are connecting to Oracle you'll need to rewrite without `TOP` – Martin Smith Feb 22 '14 at 20:08
I am definitely connecting to SQL Server using SQLDeveloper by Oracle – redrubia Feb 23 '14 at 15:21
@redrubia - Never used it myself. Looks like you need to [`SET DEFINE OFF`](http://stackoverflow.com/questions/1137354/oracle-pl-sql-escape-character-for-a). – Martin Smith Feb 23 '14 at 15:24

1 Answers1