IS-NUMERIC functions

Analytics
N/A

IS-NUMERIC functions

Hello,
this is my problem:
I need to find out with a query, all the non numeric charaters in a field. The field is a varchar but should contain only telephone number, does exist a ISNUMERIC funtions????

select * from table where not field is numeric

Thank you
regards
16 REPLIES
Enthusiast

Re: IS-NUMERIC functions

There is a "to_number" UDF posted on Teradata's UDF site with the "Oracle" UDF's.

Using this function, if the string cannot be converted to a number, the function will return a NULL. So, you can simply select from the table where the UDF call results in a NULL and it should give you all the rows with bad numbers.

Enthusiast

Re: IS-NUMERIC functions

You can try this

Position will ensure that we have taken care or space in between two numbers.

SEL
sno -- CHAR type
,CASE WHEN (POSITION(' ' IN TRIM(sno)) > 0) OR (UPPER(TRIM(sno)) (CASESPECIFIC) <> LOWER(TRIM(sno)) (CASESPECIFIC))
THEN 'Non-Numeric'
ELSE 'Numeric' End
FROM Test_Table
ORDER BY 2,1;

Instead of UDF.

Vinay
Teradata Employee

Re: IS-NUMERIC functions

the UDF to_number function returns 7504 error if you pass it a non-numeric: "INVALID CHARACTER .. IN NUMBER STRING"

Enthusiast

Re: IS-NUMERIC functions

If you use the "to_number_3.c" code, it will return a NULL if the number is not valid. Sorry for not clarifying earlier.
Enthusiast

Re: IS-NUMERIC functions

After looking at this again, I see that Jeff is right...even the "to_number_3.c" code returns the 7504 message. In order to return a NULL, you'll need to go into the funtion and modify it to issue a "return;" in the places that it detects the various errors. Since the function sets the result to NULL at the beginning of the function, a "return;" will cause the function to return a NULL.
Enthusiast

Re: IS-NUMERIC functions

Hi flaska,

I use an UDF from Mr. Dennis Calkins named is_integer.

This function returns 0 if field not numeric and 1 if numeric
U can use it like this:
select * from mydatabase.emp_table where mydatabase.is_numeric(emp_name)=1;
(assumed u have an emp_table whith the field emp_name)
or
select mydatabase.is_numeric('1234A567'); (this returns a 0)

In the attachment is the hole code
(Registration in the TD database and the c-Code for Unix u must change the .txt in .c)
In addition u can find the original at: http://www.teradataforum.com/is_integer.htm

hth

stami
Enthusiast

Re: IS-NUMERIC functions

Try this...

select * from table
where field <> upper(field)

This is a trick that works MOST of the time since numeric characters do not UPPER.
Enthusiast

Re: IS-NUMERIC functions

Hi,
This will help you:

select * from table where trim(field) not between '0' and '9999999999999999'

Regds,
Sachin

Re: IS-NUMERIC functions

Thank you - worked like a treat!!