aola aola - 2 months ago 28
SQL Question

how to check the last two characters are digits in hive?

in sql I use

regexp_instr(substr(trim(col),1,2),'^([0-9]){2}$') = 1


but I not sure how can I check this in hive, I tried
regexp_extract
instead
regexp_instr
but I just have the last digit then.

Answer

I would expect something like this to work:

where col rlike '[0-9]{2}$'

Your code has a trim(); you can include that logic in the expression as well:

where col rlike '[0-9]{2}[ ]*$'

I would recommend such simplified logic in your original system as well.