How do I escape a percentage sign in T-SQL?

Sql ServerTsql

Sql Server Problem Overview

This question also has the answer, but it mentions DB2 specifically.

How do I search for a string using LIKE that already has a percent % symbol in it? The LIKE operator uses % symbols to signify wildcards.

Sql Server Solutions

Solution 1 - Sql Server

Use brackets. So to look for 75%

WHERE MyCol LIKE '%75[%]%'

This is simpler than ESCAPE and common to most RDBMSes.

Solution 2 - Sql Server

You can use the ESCAPE keyword with LIKE. Simply prepend the desired character (e.g. '!') to each of the existing % signs in the string and then add ESCAPE '!' (or your character of choice) to the end of the query.

For example:

SELECT *
FROM prices
WHERE discount LIKE '%80!% off%'
ESCAPE '!'

This will make the database treat 80% as an actual part of the string to search for and not 80(wildcard).

MSDN Docs for LIKE

Solution 3 - Sql Server

WHERE column_name LIKE '%save 50[%] off!%'

Solution 4 - Sql Server

You can use the code below to find a specific value.

WHERE col1 LIKE '%[%]75%'

When you want a single digit number after the% sign, you can write the following code.

WHERE col2 LIKE '%[%]_'

Solution 5 - Sql Server

In MySQL,

WHERE column_name LIKE '%|%%' ESCAPE '|'

Categories
Recommended Sql Server Solutions
Recommended Tsql Solutions

Previous Solution:

How do I purge a linux mail box with huge number of emails?

EmailPurge

Next Solution:

How do you move a commit to the staging area in git?

GitCommitStaging

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJedidjaView Question on Stackoverflow
Solution 1 - Sql ServergbnView Answer on Stackoverflow
Solution 2 - Sql ServerJedidjaView Answer on Stackoverflow
Solution 3 - Sql ServerAaron BertrandView Answer on Stackoverflow
Solution 4 - Sql ServeryvzView Answer on Stackoverflow
Solution 5 - Sql ServerKishan SolankiView Answer on Stackoverflow