sqlregexhivenumbersrlike

Regex like telephone number on Hive without prefix (+01)

We have a problem with a regular expression on hive. We need to exclude the numbers with +37 or 0037 at the beginning of the record (it could be a false result on the regex like) and without letters or space.

We're trying with this one: regexp_like(tel_number,'^\+37|^0037+[a-zA-ZÀÈÌÒÙ ]') but it doesn't work.

Edit: we want it to come out from the select as true (correct number) or false.

Solution

  • To exclude numbers which start with +01 0r +001 or +0001 and having only digits without spaces or letters:

    ... WHERE tel_number NOT rlike '^\\+0{1,3}1\\d+$'

    Special characters like + and character classes like \d in Hive should be escaped using double-slash: \\+ and \\d.