to check is a string is numeric or not

UDA

to check is a string is numeric or not

[font=Arial][/font]
Hi,
Is there any UDF to check if a string (in my case its CHAR(9)) is numeric or contains special chars/alphabets?

My requirement is... i need to consider only those records which are numeric and populate NULL for rest of the records. The values in string can be: 127684,9749-id etc.

Can i use is_numeric() for string? The only other option is to check each letter in the string and if they are between 0-9.

Thanks in advance.
14 REPLIES
Teradata Employee

Re: to check is a string is numeric or not

Hello,

Till Teradata V2R6, no there is no built-in function for that, but offcourse you can write a UDF which can do that.

Regards,

Adeel
N/A

Re: to check is a string is numeric or not

Use the upper and lower function to achieve your result.
Eg: upper(colum1)=lower(colum1) if it is number it will match else it will not match.
This is the simplest way to solve your problem.

Re: to check is a string is numeric or not

Upper(col1)=lower(col1) will not work.

Ex:
say the column col1 = '6-98-89 9' which fits in char(9).
Now Upper(col1)=lower(col1) will be same. In this case it would pass the check & while inserting the process aborts saying bad character in the field.

Thnx,
Guru

Re: to check is a string is numeric or not

Guru,

Here's a previous posting on the board that may be of use to you:

http://www.teradata.com/teradataForum/shwmessage.aspx?forumid=9&messageid=3272#bm3297

BoB

Re: to check is a string is numeric or not

The link posted by rkeith01 doesn't work for me, anyone know how to get to the post?

Re: to check is a string is numeric or not

CHAR2HEXINT(UPPER(col1)) NE CHAR2HEXINT(LOWER(col1)) will go a long way to solving your issue. it sometimes has issues with special characters but you can filter on those.

R

Re: to check is a string is numeric or not

If you know the list of symbols that u can get in the field then here is a very easy solution.

scenario 1:
SELECT '12768A4',CASE WHEN UPPER('12768A4') LIKE ANY ('%A%','%B%','%C%','%D%','%E%','%F%','%G%','%H%','%I%' ,'%J%','%@%','%#%','%$%','%^%','%&%','%*%','%(%','%)%' ) THEN 'CONTAINS ALPHABET OR SYMBOLS' ELSE 'ALL INTEGER' END AS FLAG

scenario 2:
SELECT '127@684',CASE WHEN UPPER('127@684') LIKE ANY ('%A%','%B%','%C%','%D%','%E%','%F%','%G%','%H%','%I%' ,'%J%','%@%','%#%','%$%','%^%','%&%','%*%','%(%','%)%' ) THEN 'CONTAINS ALPHABET OR SYMBOLS' ELSE 'ALL INTEGER' END AS FLAG

scenario 3:
SELECT '127684',CASE WHEN UPPER('127684') LIKE ANY ('%A%','%B%','%C%','%D%','%E%','%F%','%G%','%H%','%I%' ,'%J%','%@%','%#%','%$%','%^%','%&%','%*%','%(%','%)%' ) THEN 'CONTAINS ALPHABET OR SYMBOLS' ELSE 'ALL INTEGER' END AS FLAG

Note: The list is incomplete please make change accordingly.

Hope this will help!!!

Re: to check is a string is numeric or not

The trouble with using the LIKE ANY phrase like that is that it is EXTREMELY resource intensive. Think about what it has to do. I would imagine the explain plan for them is a nightmarish forest of OR statements.

R
Teradata Employee

Re: to check is a string is numeric or not

I'm going to face a problem like this.  

I have a query to tune that does joins to own table using like any conditions.  The pseudo code would be something like 

SEL A.COL1, A.COL2

FROM TABLE1 A LEFT OUTER JOIN TABLE2 B

WHERE A.COL1 = B.COL1 

AND     B.COL2 LIKE ANY ( SEL C.COL1 FROM TABLE1 C WHERE C.COL2 LIKE '%@%%' ESCAPE '@') LEFT OUTER JOIN TABLE1 Z 

ON Z.COL2 LIKE ANY ( ...

i don't get it, why would people write such? If i have a million rows for TABLE2 and gazillion rows for TABLE1.  That would send the teradatabox to oblivion, why would people do such use those like any functions?  any performance alternative?