Re: selecting for type cast failures

On 3/7/2013 8:08 PM, Natalie Wenz wrote:
> I am working on updating some of our tables to use appropriate native
> data types; they were all defined as text when they were created
> years ago.
>
> What I am running into, though, is there are some records that have
> bad data in them, where they can't be successfully converted to int,
> or float, or boolean, for example.
>
> Is there a straightforward way to identify offending records?
>
> I've been able to identify some with things like "...not similar to
> '(0|1)'..." for the boolean fields, and "...not similar to
> '[0-9]{1,}'..." for int. Are regular expressions the best approach
> here or is there a better way?

I did some quick searching also, looks like regular expressions are your
way to go. Here is one for isInteger, for example:

varchar ~ '^[0-9]+$'

--
Guy Rouillier