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
-- If you are stuck at something .... consider it an opportunity to think anew.
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.
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
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#bm3297BoB
The link posted by rkeith01 doesn't work for me, anyone know how to get to the post?
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
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!!!
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
[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.