All Forums UDA
06 Jun 2008
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.

Adeel Chaudhry 773 posts Joined 04/08
08 Jun 2008

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.

Vinu 2 posts Joined 08/06
12 Jun 2008

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.

12 Jun 2008

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

rkeith01 4 posts Joined 10/05
12 Jun 2008

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

teradatatester 69 posts Joined 01/10
11 Jul 2011

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

rluebke 65 posts Joined 11/05
12 Jul 2011

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

indra_tech 10 posts Joined 07/11
15 Jul 2011

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!!!

rluebke 65 posts Joined 11/05
19 Jul 2011

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

kelvsdotph 14 posts Joined 06/13
29 Sep 2013

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?  
 

dnoeth 4628 posts Joined 11/04
29 Sep 2013

Are you actually joining the same table several times?
What is this query trying to achieve? Could you share the actual query?
Any join using LIKE (and any non equi-join) will result in a product join, if there's an additional join condition using equality it might be ok, otherwise ...
 
Dieter

Dieter

NBGTD 2 posts Joined 06/13
06 Nov 2013

How about some thing like where columnname not between -99999999999999 and 99999999999999.
Norman

dipanjam 7 posts Joined 06/11
02 Apr 2015

Okay.. so here it is. My requirement was we have a column which is varchar(30) and can hold any string and I need to find out which one are 14 byte, and each of the 14 bytes is a numeric value(0-9).
And I wrote a query which worked.

sel  C1 FROM Table1

where c1 between '10000000000000' and '99999999999999'

and c1 not like ('% %')

AND LENGTH(TRIM(c1)) = 14

 

Hope this helps :)

Dips

dnoeth 4628 posts Joined 11/04
02 Apr 2015

Hi Dips,
this will fail if the string starts with a digit, e.g. '1qwertzuiopü+?' will pass.
What's your Teradata release?
There are several ways in TD14:

WHERE REGEXP_SIMILAR(TRIM(c1), '^[0-9]{14}$') = 1

WHERE RTRIM(TRIM(c1), '0123456789') = ''
  AND LENGTH(TRIM(c1)) = 14

 

Dieter

samukjj 1 post Joined 01/15
14 Oct 2015

You can try:
select case when to_number('123') is null then 'not numeric' else 'numeric' end as field_test
So, you can replace '123' for your variable/column/field/etc.
 

You must sign in to leave a comment.