CODEWITHSUNDEEP
mysqlsqlwildcardsql-like
Giochi Blu
Giochi Blu

Reputation: 184

SQL - How to use wildcard in LIKE as a normal character

if I have a column with some values that starts with "%" like this:

[ID]-----[VALUES]
1--------Amount
2--------Percentage
3--------%Amount
4--------%Percentage

how can I have only these two rows with a "select" query?:

[ID]-----[VALUES]
3--------%Amount
4--------%Percentage

I tried these queries but them don't work:

select * from TABLE where VALUES like '[%]%'
select * from TABLE where VALUES like '\%%'

I know that in Java, C and other languages, the backspace \ let you use a jolly character as a normal one like:

var s = "I called him and he sad: \"Hi, there!\"";

There is a similar character/function that do it in SQL?
All answers will be appreciated, thank you for reading the question!

Upvotes: 2

Views: 2474

Answers (4)

GryffensBane
GryffensBane

Reputation: 33

You're right that you'll need an escape character for this. In SQL you have to define the escape character.

SELECT * FROM TABLE where VALUES like ESCAPE '!';

I'm pretty sure you can use whatever character you want.

Here's a link to a microsoft explanation that goes into more detail.

Microsoft explanation

MySQL Explanation

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Your query 

select * from TABLE where VALUES like '\%%'

should work. The reason it doesn't is because you may have NO_BACKSLASH_ESCAPES enabled which would treat \ as a literal character.

A way to avoid it is using LIKE BINARY 

select * from TABLE where VALUES like binary '%'

or with an escape character (can be any character you choose) specification.

select * from TABLE where VALUES like '~%%' escape '~'

Upvotes: 2

puzeledbysql
puzeledbysql

Reputation: 194

try this :

select * from TABLE where VALUES like  '%[%]%'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271051

There is an ESCAPE option on LIKE:

select *
from TABLE
where VALUES like '$%%' escape '$';

Anything following the escape character is treated as a regular character. However, the default is backslash (see here), so the version with backslash should do what you want.

Of course, you could also use a regular expression (although that has no hope of using an index).

Note: escape is part of the answer standard so it should work in any database.

Upvotes: 1

Related Questions