I am trying to search for this pattern ab1234. I tried col like 'ab[0-9][0-9][0-9][0-9]' col like '(ab)[0-9]{4}' col like 'ab####' None of these are working. I checked this website https://www.w3schools.com/sql/sql_wildcards.asp, but it is not very helpful. Any advice is appreciated :)
Asked
Active
Viewed 251 times
1
-
Are there any preceding or trailing characters in addition to AB1234? Do you want to look for lowercase ab or uppercase AB? – donPablo Feb 07 '21 at 07:07
-
@donPablo sorry, that was a typo. I am only looking for lower case. thank you – Rachel Y Feb 07 '21 at 07:11
-
use `rlike` instead of `like` for matching regular expressions. `col rlike 'ab[0-9][0-9][0-9][0-9]'` – mck Feb 07 '21 at 07:46
-
@mck thank you, but the results were giving all the row contain this pattern – Rachel Y Feb 07 '21 at 07:52
-
Isn't that what you're trying to do??? – mck Feb 07 '21 at 07:52
2 Answers
1
Use rlike for regexp check:
col rlike 'ab\\d{4}' --containing ab and 4 digits in any place of the string
Or more strict pattern:
col rlike '^ab\\d{4}$' --Exactly ab and 4 digits, no other characters before or after
leftjoin
- 36,950
- 8
- 57
- 116
1
select * from table where col rlike 'ab[0-9]{4}'
Amardeep Flora
- 1,255
- 6
- 13
- 29
-
The community encourages adding explanations alongisde code, rather than purely code-based answers (see [here](https://meta.stackoverflow.com/questions/300837/what-comment-should-i-add-to-code-only-answers)). – costaparas Feb 08 '21 at 05:24