Function Isnumeric

[LUIS CARLOS DIAZ OTERO]

Use any valid numeric expression inside yor expression
and then look at the error code

--- Rein Puksand <rpuk...@epbe.ee> escribió: > HI,
>
> In Visual Basic(access) is function Isnumeric.
> (IsNumeric returns True if the entire expression is
> recognized as a number;
> otherwise, it returns False.)
>
> I need the same function in Informix. (or) How can I
> do this ?
>
> Thanks in advance
> Rein
>

=====
______________________________________
Luis Carlos Díaz Otero
ALCANOS DE COLOMBIA S.A. E.S.P
Carrera 9 # 7-25
Neiva (Huila (Colombia))

__________________________________________________
Do You Yahoo!?
Yahoo! Mail - Free email you can access from anywhere!
http://mail.yahoo.com/

[Rein Puksand]

I don't understand. (maybe I didn't express explicitly.)

Example:
I have table: TEMP
num cont (Text data type)
-------------------------------
01 kkkd34
02 ddde
03 112
04 23xk
05 0000
06 krook00
07 kk34
08 12

I want to find rows where cont is only numeric values.
In ACCESS I use:
SELECT num,cont
FROM TEMP
WHERE ((IsNumeric([count]))=True);

And result is:

num cont
-----------------
03 112
05 0000
08 12

How I do this in Informix (SQL)?

Rein

[Art S. Kagel]

That depends on a few things. First, what version of Informix are you
running? If IDS.2000 you can build the isnumeric() function as a UDF (User
Defined Function) in C (or in Java if you have Foundation.2000) and add it
to the engine in a shared library (RTM). If you have IDS 7.xx or 8.xx you
have to code a stored procedure to do this which will be slower. Informix
has no equivalent built-in function.

BTW Luis's suggestion works. Just say:

SELECT (cont + 0)

and if you receive an error in the -1200's range, usually -1213, then the
row contains a non-numeric character.

In addition you say cont it type Text is that a TEXT BLOB type column or a
char or varchar type column? If type TEXT it cannot be searched using SQL
except on IDS.2000 using a text datablade like Excaliber.

Art S. Kagel

[Rudy Fernandes]

Rein Puksand wrote:

> I have table: TEMP
> num cont (Text data type)
> -------------------------------
> 01 kkkd34
> 02 ddde
> 03 112
> 04 23xk
> 05 0000
> 06 krook00
> 07 kk34
> 08 12
>
> I want to find rows where cont is only numeric values.

> How I do this in Informix (SQL)?
>

The statement below will eliminate rows that have any alphabet (lower or
uppercase) or the characters "!", "^" in the column cont.

select num, cont
from temp
where cont NOT MATCHES '*[a-zA-Z!^]*';

HTH
Rudy