SQL LIKE only one letter

Question

When i use LIKE statement in my SQL,

for example,

      SELECT * FROM table WHERE name = "%k"

It will return all rows, where name ends on k.

It can return : Ok, OOk, OOOk,

How i can do same statement but with one letter, so it returns only Ok.

Or 2 letters, so it returns only OOk?

Kevin Bowersox · Answer 1 · 2013-12-11T10:42:44.180

9

_ is a single character wildcard.

SELECT * FROM table WHERE name = `_k`

edited Dec 11 '13 at 10:42

answered Dec 11 '13 at 10:33

Kevin Bowersox

score 0 · Answer 2 · answered Dec 11 '13 at 10:33

0

Use the equality operator (=) or the IN operator instead of the LIKE operator:

SELECT * FROM table WHERE name IN ('Ok', 'OOk')

answered Dec 11 '13 at 10:33

Dan

There are more rows, i dont need only two. I need sql which select the rows ending on 'k' and having length 2 characters – user3090649 Dec 11 '13 at 10:34
In that case, use `WHERE name LIKE '%k' AND LEN(name) = 2` – Dan Dec 11 '13 at 10:35

score 0 · Answer 3 · answered Dec 11 '13 at 10:35

0

Use the _ wildcard. It matches only a single character.

_k for Ok. __k for Ook

answered Dec 11 '13 at 10:35

atripathi

score 0 · Answer 4 · answered Dec 11 '13 at 10:35

0

depending on the length you provide

For 1 character 'X' followed by k

Select * 
from table 
where length(name) = 2 
AND name = "%k"

For 2 character 'X' followed by k

Select * 
from table 
where length(name) = 3
AND name = "%k"

answered Dec 11 '13 at 10:35

Schuere

score 0 · Answer 5 · answered Dec 11 '13 at 10:37

0

You should use an underscore (_) character. See the documentation about operator LIKE.

So, the query you need (1 or 2 chars before k) is:

SELECT * FROM table WHERE name LIKE '_k' OR name LIKE '__k'

answered Dec 11 '13 at 10:37

Serge S.

5 Answers5