How to search two strings followed by 4 digits in Hive SQL

Question

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 :)

Answer 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

Answer 2

select * from table where col rlike 'ab[0-9]{4}'