All Forums Analytics
flaska 1 post Joined 07/06
06 Jul 2006
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 numericThank you regards

Barry-1604 176 posts Joined 07/05
06 Jul 2006

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.

BBR2 96 posts Joined 12/04
06 Jul 2006

You can try thisPosition will ensure that we have taken care or space in between two numbers.SELsno -- CHAR type,CASE WHEN (POSITION(' ' IN TRIM(sno)) > 0) OR (UPPER(TRIM(sno)) (CASESPECIFIC) <> LOWER(TRIM(sno)) (CASESPECIFIC)) THEN 'Non-Numeric' ELSE 'Numeric' EndFROM Test_TableORDER BY 2,1;Instead of UDF.Vinay

j355ga 100 posts Joined 12/05
10 Jul 2006

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


Barry-1604 176 posts Joined 07/05
11 Jul 2006

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.

Barry-1604 176 posts Joined 07/05
11 Jul 2006

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.

stami27-2406 22 posts Joined 02/06
12 Jul 2006

Hi flaska,I use an UDF from Mr. Dennis Calkins named is_integer.This function returns 0 if field not numeric and 1 if numericU 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:

ThisIsBob 6 posts Joined 09/06
19 Sep 2006

Try * from table where field <> upper(field)This is a trick that works MOST of the time since numeric characters do not UPPER.

sachinp17 53 posts Joined 11/06
15 Nov 2006

Hi, This will help you:select * from table where trim(field) not between '0' and '9999999999999999' Regds,Sachin

Fogster74 3 posts Joined 03/07
30 Mar 2007

Thank you - worked like a treat!!

Fogster74 3 posts Joined 03/07
30 Mar 2007

Checking for a date.I was searching for a way to exclude non-valid values for a field in which the format should be YYYYMMDD.Earliest date was 01-01-1901, latest date was 31-12-3499 - UK date format ;-)I used:CASE WHEN ( CAST ( mydate AS INTEGER) BETWEEN 19010101 AND 34991231 THEN CAST (TRIM (mydate) AS DATE FORMAT 'YYYYMMDD') ELSE NULLENDI'm not sure but the TRIM here may actually be redundant but I've left it in as, if spaces are counted when the system tries to take this value as a date, I can;t see it doing any harm...Although this (now) seems incredibly simple, I thought it may be of use to others.Cheers!S

stefamagni 2 posts Joined 06/09
09 Jun 2009

Hi,the following select demonstrates that this solution is not complete:selectCASE WHEN trim('0101000') BETWEEN '0' AND '99999999999999999999' THEN '0101000 is castable to Integer'ELSE '0101000 is NOT castable to Integer'END AS "0101000",CASE WHEN trim('010-000') BETWEEN '0' AND '99999999999999999999' THEN '010-000 is castable to Integer'ELSE '010-000 is NOT castable to Integer'END AS "010-000",CASE WHEN trim('010/000') BETWEEN '0' AND '99999999999999999999' THEN '010/000 is castable to Integer'ELSE '010/000 is NOT castable to Integer'END AS "010/000",CASE WHEN trim('parola') BETWEEN '0' AND '99999999999999999999' THEN 'Parola is castable to Integer'ELSE 'parola is NOT castable to Integer'END AS "parola",CASE WHEN trim(' ') BETWEEN '0' AND '99999999999999999999' THEN 'Blank is castable to Integer'ELSE 'Blank is NOT castable to Integer 'END AS "Blank";I think that the only universal solution is to use a UDF

ogguz 3 posts Joined 09/11
14 Sep 2011

REPLACE FUNCTION syslib.isinteger(X VARCHAR(20))
        CHARACTERS(X) > 18 -- first elimination biggers from bigint
    THEN 0
        TRANSLATE_CHK(X USING unicode_to_latin) <> 0
    THEN 0
        UPPER(X)(CASESPECIFIC) <> LOWER(X)(CASESPECIFIC) -- for letter control
    THEN 0
        CHAR2HEXINT(X) LIKE ANY -- for other controls
        '%A%','%B%','%C%','%D%','%E%','%F%', -- if hexadecimal codes have a letter that can't be a integer
        '%0020%','%0021%','%0022%','%0023%','%0024%','%0025% ','%0026%','%0027%','%0028%','%0029%','%0040%','%0060% ' -- and other characters
    THEN 0
    ELSE 1


I think this the best "With Teradata SQL" solution.

eltonberci 1 post Joined 04/11
31 Mar 2015

I am 4 years late...
This wil return 0 or 1 like the function "isnumeric" from Sql Server

downloads_2014 3 posts Joined 08/14
01 Jul 2015

I think it should be just:
select to_number( charval )
otherwise you're missing values like "0", "0.00" etc.

rnekkanti 12 posts Joined 05/15
15 Dec 2015

Thanks for the posting, eltonberci even if it 4 years late :).


missgate 3 posts Joined 01/14
30 Aug 2016

I know is to late.


Regex could be a good option.

Consider the value is not a number then a default value can set and the "SQLSTATE 22023 Invalid character x in number string" does not cause the row be rejected.


If the charval is not a number the row are be rejected.

SELECT '1224x' (CHAR(5)) AS charval
      ,TO_NUMBER(charval) AS number1
      ,'A' col1
      ,'B' col2

With regex could set a default value and the row can be inserted.

SELECT '1224x' (CHAR(5)) AS charval
      ,CASE WHEN REGEXP_SIMILAR(charval,'\d+','c') = 1 THEN charval ELSE NULL END (SMALLINT) AS number1
      ,CASE WHEN REGEXP_SIMILAR(charval,'\d+','c') = 1 THEN charval ELSE 0 END (SMALLINT) AS number2
      ,'A' col1
      ,'B' col2




You must sign in to leave a comment.