On 25-4-2015 08:08, 'Walter R. Ojeda Valiente'
sistemas2000profesional@... [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