How to Escape Back Slash in SQL Server

How to escape back slash in SQL server

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

Escaping backslash in SQL Server GRANT

GRANT SELECT ON tableName TO [something\user_name]

SQL: escaping with backslash

By default the \ doesn't really have any special meaning in a string/character constant in SQL. Single quotes are "escaped" by doubling them in SQL e.g. 'Arthur''s house'

The only situation where it does have a special meaning is the ESCAPE clause. The default escape character to escape wildcards for SQL LIKE is the \ but it can be changed through the ESCAPE clause.

So the following conditions are identical:

t.name LIKE 'FOO\_%'
t.name LIKE 'FOO\_%' ESCAPE '\'
t.name LIKE 'FOO#_%' ESCAPE '#'

But for strings that are not used for a LIKE condition, the backslash has no special meaning in "SQL", i.e. in ANSI standard SQL.

Some database products however do support non-standard escaping of characters using \ inside regular strings. Postgres used to do that, but this was deprecated a long time, and the default behaviour today is, that a \ does not escape anything in a string constant, so '\t' is simply a backslash character followed by a t. It can be configured to accepe non-standard strings where that would result in the "tab character" instead.

I think MySQL always uses this non-standard behaviour of a backslash inside a string constant.

How do apply SQL like on "\detail1\detail2\" (Escaping '\')?

Try this, you need to escape backslashes twice in LIKE statement.

select details from T1 where details like '\\\\%\\\\%\\\\'

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

T-SQL - How to escape a slash / in square brackets in LIKE clause

DECLARE @temp TABLE ( Location NVARCHAR(50) )

INSERT @temp (Location ) 
VALUES ('US/New York/A') 
    , ('New York/B') 
    , ('Japan/Tokyo/A') 
    , ('Tokyo/B') 
    , ('UK/London/A') 
    , ('London/B')

Select * 
From @temp  
Where Location Like '%/A'

There is no need to escape the / in this case. You can simply use an expression with a trailing wildcard.

Edit based on change to OP

It appears you may have a misconception about how the [] pattern is interpreted in the LIKE function. When you have a pattern like '[US/New York]%', it is saying "Find values that start with any of the following characters U,S,/,N,e,w, (space), Y, o,r, or k. Thus, such a pattern would find a value South Africa or Outer Mongolia. It isn't looking for rows where the entire value is equal to US/New York.

One way to achieve what you seek is it to use multiple Or statements:

Select *
From @temp
Where Location Like 'US/New York%'
    Or Location Like 'Japan/Tokyo%'
    Or Location Like 'UK/London%'

How to reference a sql server with a backslash (\) in its name?

In 4 part names, the first part if the name of a linked server (ie. a metadata object), not the name of a server (ie. a host name). So you can name your linked server FOO and have him point at the host BAR, or at the instance FOO\BAR. And even if you name the linked server object to contain a slash, you can still use it in a multi-part name by simply quoting the name:

SELECT TOP 1 *  
FROM [DevServerB\2K5].master.sys.tables

sql query to replace backslashes '\\' with '\/'

You must escape each backslash with a double backslash:

SELECT REPLACE(mycolumn, '\\\\', '\\/') 
FROM mytable
WHERE mycolumn LIKE '%photos%';

Or you can update the table:

UPDATE mytable
SET mycolumn = REPLACE(mycolumn, '\\\\', '\\/')
WHERE mycolumn LIKE '%photos%';

and the column will contain the values as you want them.

See the demo.

---

Related Topics