All Forums Database
raj_tera 8 posts Joined 03/15
25 Mar 2015
How to find if a varchar field has 9 digit intergers

Hi,
I would like to know how to find if a varchar field has integer values with length 9? this field is 90 chars in the table. the challange is the value 9 digit integer could be mixed with varchar values like this, sample -
 
sr#985301223
sln932876532 ars
prm564321560tyr
Teradata version is 14, I can't create/install any udf. please help.
 

dnoeth 4628 posts Joined 11/04
25 Mar 2015

You don't need a UDF, TD14 supports regular expressions.
Do you want only those rows where the field has exactly 9 digits?

where REGEXP_SIMILAR(x, '^[0-9]{9}$') = 1

Or extract those 9 digits from the field regardless of other characters?

REGEXP_SUBSTR(x, '[0-9]{9}')

 

Dieter

raj_tera 8 posts Joined 03/15
27 Mar 2015

Hi dnoeth,
 
Thanks much for your response. I tried both REGEXP functions you provided, I get the error - Error Code 6706: The string contains an untranslatable character.
this field is an address field, so any type of chars are expected in this field.. like #, - etc..I am not sure if this is the reason. Could you please let me know what could be the issue here..?
 
Also, for REGEXP_SIMILAR(x, '^[0-9]{9}$') = 1 , I have to use i, like this- as I got syntax error..
 
REGEXP_SIMILAR(x, '^[0-9]{9}$', 'i') = 1
 
 

dnoeth 4628 posts Joined 11/04
27 Mar 2015

What's your exact TD release, TD14?
Is the source defined as LATIN or UNICODE?
 
There have been some bugs regarding the RegEx functions, this should have been fixed in recent patch levels...

Dieter

raj_tera 8 posts Joined 03/15
30 Mar 2015

I am on TD 14.10.0.11..
 
not sure how to check on source defined as LATIN or UNICODE? could you pls help.
 
Is there any other function instead? Thanks!

raj_tera 8 posts Joined 03/15
31 Mar 2015

Hi, is there any other way to find this out? I would like to know how to find if a varchar field has integer values with length 9? this field is 90 chars in the table. the challange is the value 9 digit integer could be mixed with varchar values like this, sample -
 
sr#985301223
sln932876532 ars
prm564321560tyr
Teradata version is 14, I can't create/install any udf. please help.

Fred 1096 posts Joined 08/04
01 Apr 2015

Try explicitly translating the literals (which are implicitly Unicode) to Latin:
 
REGEXP_SIMILAR(x, translate('[0-9]{9}' using Unicode_to_Latin), translate('i' using Unicode_to_Latin)) = 1

You must sign in to leave a comment.