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