How to find if a varchar field has 9 digit intergers

Database
Enthusiast

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.

6 REPLIES
Senior Apprentice

Re: How to find if a varchar field has 9 digit intergers

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}')
Enthusiast

Re: How to find if a varchar field has 9 digit intergers

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

 

Senior Apprentice

Re: How to find if a varchar field has 9 digit intergers

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

Enthusiast

Re: How to find if a varchar field has 9 digit intergers

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!

Enthusiast

Re: How to find if a varchar field has 9 digit intergers

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.

Teradata Employee

Re: How to find if a varchar field has 9 digit intergers

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