Re: [firebird-support] Test VARCAHR for numeric
Thanks to everyone for all the feedback. if (param IS SIMILAR '[0-9]+') then Did everything I needed it to do. Thanks again.
Re: [firebird-support] Test VARCAHR for numeric
Very interesting Mark, I shall remember that for the time when Firebird3 is
coming here.
Greetings.
Walter.
On Sat, Apr 25, 2015 at 3:14 AM, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] wrote:
>
>
> On 25-4-2015 08:08, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] wrote:
> > This article:
> >
> >
> https://firebird21.wordpress.com/2015/04/23/validando-que-el-contenido-de-un-char-o-varchar-sea-numerico/
> >
> > 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)
>
> With Firebird 3 functions you can define a function isvalidint:
>
> CREATE FUNCTION isvalidint(stringValue VARCHAR(25))
> RETURNS BOOLEAN
> AS
> DECLARE intValue INT;
> BEGIN
> BEGIN
> intValue = CAST(stringValue AS INT);
> RETURN TRUE;
> END
> WHEN ANY DO
> RETURN FALSE;
> END
>
> Example table:
> CREATE TABLE tableIntAsVarchar (
> ID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
> intAsVarchar VARCHAR(25) NOT NULL
> );
>
> Sample data:
> INSERT INTO tableIntAsVarchar (intAsVarchar) VALUES ('123');
> INSERT INTO tableIntAsVarchar (intAsVarchar) VALUES ('-123');
> INSERT INTO tableIntAsVarchar (intAsVarchar) VALUES ('Not a valid int');
>
> SELECT * FROM tableIntAsVarchar WHERE NOT isvalidint(intAsVarchar)
>
> Will only return the last inserted row (with value "Not a valid int").
>
> Similarly you could create a isValidBigint, isValidDoublePrecision etc.
>
> Mark
> --
> Mark Rotteveel
>
>
Re: [firebird-support] Test VARCAHR for numeric
On 25-4-2015 08:08, 'Walter R. Ojeda Valiente'
sistemas2000profesio...@gmail.com [firebird-support] wrote:
> This article:
>
> https://firebird21.wordpress.com/2015/04/23/validando-que-el-contenido-de-un-char-o-varchar-sea-numerico/
>
> 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)
With Firebird 3 functions you can define a function isvalidint:
CREATE FUNCTION isvalidint(stringValue VARCHAR(25))
RETURNS BOOLEAN
AS
DECLARE intValue INT;
BEGIN
BEGIN
intValue = CAST(stringValue AS INT);
RETURN TRUE;
END
WHEN ANY DO
RETURN FALSE;
END
Example table:
CREATE TABLE tableIntAsVarchar (
ID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
intAsVarchar VARCHAR(25) NOT NULL
);
Sample data:
INSERT INTO tableIntAsVarchar (intAsVarchar) VALUES ('123');
INSERT INTO tableIntAsVarchar (intAsVarchar) VALUES ('-123');
INSERT INTO tableIntAsVarchar (intAsVarchar) VALUES ('Not a valid int');
SELECT * FROM tableIntAsVarchar WHERE NOT isvalidint(intAsVarchar)
Will only return the last inserted row (with value "Not a valid int").
Similarly you could create a isValidBigint, isValidDoublePrecision etc.
Mark
--
Mark Rotteveel
Re: [firebird-support] Test VARCAHR for numeric
Hello Mark This article: https://firebird21.wordpress.com/2015/04/23/validando-que-el-contenido-de-un-char-o-varchar-sea-numerico/ 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 m...@lawinegevaar.nl [firebird-support] wrote: > > > On Thu, 23 Apr 2015 20:00:32 -0400, "'Walter R. Ojeda Valiente' > sistemas2000profesio...@gmail.com [firebird-support]" > 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 > >
Re: [firebird-support] Test VARCAHR for numeric
On Thu, 23 Apr 2015 20:00:32 -0400, "'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]" 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
Re: [firebird-support] Test VARCAHR for numeric
Again, if you can read Spanish then the following article has more examples: https://firebird21.wordpress.com/2015/04/23/validando-que-el-contenido-de-un-char-o-varchar-sea-numerico/ Greetings. Walter. On Thu, Apr 23, 2015 at 8:03 PM, Walter R. Ojeda Valiente < sistemas2000profesio...@gmail.com> wrote: > Sorry, typo in the SELECT, just one comma after MyColumn2. > > Greetings. > > Walter. > > > On Thu, Apr 23, 2015 at 8:00 PM, Walter R. Ojeda Valiente < > sistemas2000profesio...@gmail.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. >> >> 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:]]*' >> >> 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/ >> >> Greetings. >> >> Walter. >> >> >> On Thu, Apr 23, 2015 at 2:34 PM, sboyd...@gmail.com [firebird-support] < >> firebird-support@yahoogroups.com> wrote: >> >>> >>> >>> Is there any way, within a stored procedure, to test a VARCHAR to see if >>> it contains a valid number? I have a parameter that can contain different >>> types of value and it would be nice to be able to know if CAST(param as >>> BIGINT) is going to fail before an exception is thrown. >>> >>> >>> >>> >>> >> >> >
Re: [firebird-support] Test VARCAHR for numeric
Sorry, typo in the SELECT, just one comma after MyColumn2. Greetings. Walter. On Thu, Apr 23, 2015 at 8:00 PM, Walter R. Ojeda Valiente < sistemas2000profesio...@gmail.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. > > 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:]]*' > > 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/ > > Greetings. > > Walter. > > > On Thu, Apr 23, 2015 at 2:34 PM, sboyd...@gmail.com [firebird-support] < > firebird-support@yahoogroups.com> wrote: > >> >> >> Is there any way, within a stored procedure, to test a VARCHAR to see if >> it contains a valid number? I have a parameter that can contain different >> types of value and it would be nice to be able to know if CAST(param as >> BIGINT) is going to fail before an exception is thrown. >> >> >> >> >> > >
Re: [firebird-support] Test VARCAHR for numeric
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. 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:]]*' 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/ Greetings. Walter. On Thu, Apr 23, 2015 at 2:34 PM, sboyd...@gmail.com [firebird-support] < firebird-support@yahoogroups.com> wrote: > > > Is there any way, within a stored procedure, to test a VARCHAR to see if > it contains a valid number? I have a parameter that can contain different > types of value and it would be nice to be able to know if CAST(param as > BIGINT) is going to fail before an exception is thrown. > > > > >
[firebird-support] Test VARCAHR for numeric
Is there any way, within a stored procedure, to test a VARCHAR to see if it contains a valid number? I have a parameter that can contain different types of value and it would be nice to be able to know if CAST(param as BIGINT) is going to fail before an exception is thrown.
