Re: Function Isnumeric
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
-----Original Message-----
From: LUIS CARLOS DIAZ OTERO [mailto:luiscarlosdiazo@yahoo.com]
Sent: 23. august 2000. A. 21:39
To: rpuksand@epbe.ee; Informix List (E-mail)
Subject: Re: Function Isnumeric
Use any valid numeric expression inside yor expression
and then look at the error code
--- Rein Puksand <rpuksand@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/
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
Rein Puksand wrote:
>
> 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
>
> -----Original Message-----
> From: LUIS CARLOS DIAZ OTERO [mailto:luiscarlosdiazo@yahoo.com]
> Sent: 23. august 2000. A. 21:39
> To: rpuksand@epbe.ee; Informix List (E-mail)
> Subject: Re: Function Isnumeric
>
> Use any valid numeric expression inside yor expression
> and then look at the error code
>
> --- Rein Puksand <rpuksand@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 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