Subject Re: [firebird-support] Test VARCAHR for numeric
Author Walter R. Ojeda Valiente
Post date 2015-04-25T06:08:08Z
Hello Mark

This article:


Also has examples con minus signs, inverse conditions, etc.

Exponential notation not, because it is not usually used with commercial applications. Any way, the idea is to show fastly whether there are o not columns CHAR or VARCHAR with numeric values. No matter if they are smallint, integer, bigint, etc.

Using exceptions you can know that there are problematic rows, but not which are. (Well, at least not so easily as a SELECT ... SIMILAR TO does)

Greetings.

Walter.


On Fri, Apr 24, 2015 at 2:53 AM, Mark Rotteveel mark@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
 

On Thu, 23 Apr 2015 20:00:32 -0400, "'Walter R. Ojeda Valiente'
sistemas2000profesional@... [firebird-support]"
<firebird-support@yahoogroups.com> wrote:
> You can use SIMILAR TO, something like it:
>
> SELECT
> MyColumn1,
> MyColumn2,,
> MyColumn3
> FROM
> MyTable
> WHERE
> MyColumn1 SIMILAR TO '[[:DIGIT:]]*'
>
> Where just the digits 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 are valid.

 I'd also check for minus signs, and take into account the maximum number
of digits in a number (eg 10 in an INTEGER, 19 or 20 in a BIGINT), but even
then there is no guarantee (eg 2,147,483,648 doesn't fit in an INTEGER,
while 2,147,483,647 does). The only sure way is too actually attempt the
conversion and handle the exception.

> If you need the decimal point too, try something like it, where the
> decimail point is required:
>
> SIMILAR TO '[[:DIGIT:]]*.[[:DIGIT:]]*'
>
> And if you can have integers and floats (decimal point can be, but not
> required), you can try:
>
> SIMILAR TO '[[:DIGIT:]]*.?[[:DIGIT:]]*'

 Don't forget the exponent notation (eg 5.203E-5)

> And, finally, if you can read Spanish, take a look at:
>
> https://firebird21.wordpress.com/2014/04/27/usando-similar-to/
>
> https://firebird21.wordpress.com/2014/11/25/validando-un-e-mail/

 Mark