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] <firebird-support@yahoogroups.com> 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 > >
