Search code examples
sqlregexhivehiveqlnumeric

sql regexp string end with ".0"

I want to judge if a positive number string is end with ".0", so I wrote the following sql: select '12310' REGEXP '^[0-9]*\.0$'. The result is true however. I wonder why I got the result, since I use "\" before "." to escape. So I write another one as select '1231.0' REGEXP '^[0-9]\d*\.0$', but this time the result is false. Could anyone tell me the right pattern?

Solution

  • Dot (.) in regexp has special meaning (any character) and requires escaping if you want literally dot:

     select '12310' REGEXP '^[0-9]*\\.0$';

    Result:

    false

    Use double-slash to escape special characters in Hive. slash has special meaning and used for characters like \073 (semicolon), \n (newline), \t (tab), etc. This is why for escaping you need to use double-slash. Also for character class digit use \\d:

    hive> select '12310.0' REGEXP '^\\d*?\\.0$';
OK
true

    Also characters inside square brackets do not need double-slash escaping: [.] can be used instead of \\.