How do I use regex on Netezza to find integers?

Question

I'm using the following function to find integers: where NZ_SQLEXTN..REGEXP_LIKE(d.ID, '[0-9]')

I just noticed that it's not picking up negative numbers. When I do where NZ_SQLEXTN..REGEXP_LIKE(d.ID, '[^0-9]'), the result set is all negative numbers.

How do I include negative numbers in the regex expression?

Answer 1

Your regex is actually matching only numbers from 0-9 it won't match negative nor floating points.

If you want to support more negative you can use:

-?[0-9]+

If you want to support negative and floating points, then you could use:

-?[0-9]+[.]?[0-9]*
or
-?\d+\.?\d*