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????
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.
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;
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.
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