Extract rows from a table with regular expression hive sql

Question

Please check the link for the result and table info. I need to query rows with value '343' in Col B with a regular expression . All columns are strings . Also please be kind enough to point any good learning materials in how to write good REGEX in Hive . Thank you

Answer 1

For Hive use this:

select * from tablename where B rlike '343';

Checking it works:

hive> select '123435' rlike '343';
OK
_c0
true

Negative test:

hive> select '12345' rlike '343';
OK
_c0
false
Time taken: 1.675 seconds, Fetched: 1 row(s)

Hive uses Java flavor regex. You can find good reference and practice here: https://regexr.com/ and of course regex101

Answer 2

this will work:

select * from tablename where regexp_like(B,'(.*)(343)(.*)');

hive equivalent is :

select * from tablename where rlike(B,'(.*)(343)(.*)');