CODEWITHSUNDEEP
sqlsql-like
Maxime
Maxime

Reputation: 29

SQL underscore in LIKE

programmers !

I didn't actually face the problem, but I was wondering how to do something... If I wanted to find data beginning by an a and having 3 characters in total, I would write: 

SELECT Client_ID
FROM Clients
WHERE Client_ID LIKE 'a__'

My question is, what if I want a word that contains 3 characters, with _ as the first character. How can I make my code understand that the first character is an underscore, and not just whatever character that exist ?

The code below would just give me any word containing 3 characters, whatever they are. But what if I want a client_ID starting with '_' and then containing 2 other characters ?

SELECT Client_ID
FROM Clients
WHERE Client_ID LIKE '___'

Thanks for your answers !

Upvotes: 2

Views: 2793

Answers (4)

Nikhil
Nikhil

Reputation: 3950

this will work :

SELECT Client_ID
FROM Clients
WHERE regexp_like(Client_ID,'^_{1}(.){2}$');

Upvotes: 0

user8447004
user8447004

Reputation:

I think you just need to escape the _ using [_] and then add the remaining ___ to capture the 3 characters. Something like this maybe:

select Client_ID from Clients where ClientID like '[_]___%'

Hope it helps!!

Upvotes: 2

Reda Meskali
Reda Meskali

Reputation: 275

SELECT Client_ID
FROM Clients
WHERE Client_ID LIKE '\_\_\_';

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can escape the wildcards in LIKE patterns. I usually define the escape character explicitly:

WHERE Client_ID LIKE '$___' ESCAPE '$'

The default escape character is a backslash, so this should work as well:

WHERE Client_ID LIKE '\___'

Upvotes: 1

Related Questions