What Does \ (Backslash) Mean in an SQL Query

What does \ (backslash) mean in an SQL query?

% is a wildcard character that matches zero or more characters in a LIKE clause.
_ is a wildcard character that maches exactly one character in a LIKE clause.

\ is a special character known as an escape character that indicates that the character directly following it should be interpreted literally (useful for single quotes, wildcard characters, etc.).

For example:

SELECT txt1 FROM T1 WHERE txt1 LIKE '_a%'

will select records with txt1 values of 'xa1', 'xa taco', 'ya anything really', etc.

Now let's say you want to actually search for the percent sign. In order to do this you need a special character that indicates % should not be treated as a wildcard. For example:

SELECT txt1 FROM T1 WHERE txt1 LIKE '_a\%'

will select records with txt1 values of 'ba%' (but nothing else).

Finally, a LIKE clause would typically contain a wildcard (otherwise you could just use = instead of LIKE). So you might see a query containing \%%. Here the first percent sign would be treated as a literal percent sign, but the second would be interpreted as a wildcard. For example:

SELECT txt1 FROM T1 WHERE txt1 LIKE '_a\%%'

will select records with txt1 values of 'da%something else', 'fa% taco', 'ma% bunch of tacos', etc.

How to handle a query with special characters / (forward slash) and \ (backslash)

The trick is to double escape ONLY the backslash; for string escapes only a single escape is needed.

For example

• The single quote ' only needs escaping once LIKE '%\'%'
• But to query backslash \ you need to double escape to LIKE '%\\\\%'
• If you wanted to query backslash+singlequote \' then LIKE '%\\\\\'%' (with 5 backslashes)

Explanation Source
excerpt:

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.

How to use literal backslash in SQL query statement?

To replicate your issue, we can write a query something like this:

declare @name varchar(50) = 'Test',
        @JobNum smallint = 12

select @name + '\\20' + @JobNum + ' JOBS\\'; 

This will return the same error:

Conversion failed when converting the varchar value 'Test\20' to data
type smallint.

But if you convert the smallint or the number field to a string like this, the error should go away:

declare @name varchar(50) = 'Test',
        @JobNum smallint = 12

select @name + '\\20' + cast(@JobNum as varchar(10)) + ' JOBS\\'

Your query should look something like this:

SELECT [tblEstimator].[Name] + '\\20' + cast([tbltask].[JobNum] as varchar(10)) 
       + ' JOBS\\' AS JobMidFilePath

I am not sure the equivalent of MS-ACESS on this, but that should resolve your issue.

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

When do I need to use a semicolon vs a slash in Oracle SQL?

It's a matter of preference, but I prefer to see scripts that consistently use the slash - this way all "units" of work (creating a PL/SQL object, running a PL/SQL anonymous block, and executing a DML statement) can be picked out more easily by eye.

Also, if you eventually move to something like Ant for deployment it will simplify the definition of targets to have a consistent statement delimiter.

Why does SparkSQL require two literal escape backslashes in the SQL query?

May be because backslash is a special symbol, used to concatenate multi-line SQLs.

sql_1 = spark.sql("SELECT \
    1 AS `col1`, '{0}' AS `col2`".format(var_1))

Why I need to double-escape (use 4 \) to find a backslash ( \ ) in pure SQL?

You escape first for the string syntax, then for LIKE syntax.

In LIKE characters % and _ have special meaning, so if you want to search for literal %, you need to use \%, and if you want to search for literal \% you need to escape the backslash as in \\%.

In string syntax " obviously has special meaning, so if you want to include quote in the string you need to escape it as \", and to include literal \" in the string you have to escape the backslash as in \\".

So in both syntaxes you have to escape \.

---

If you don't want to use \ to escape the LIKE pattern , you can use ESCAPE keyword. For example:

...  where test LIKE "a\\b%" ESCAPE '|';

This way, you'll need to write |%, |_ or || to escape these special chars.

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