String pattern match using regular expressions

Database
Enthusiast

String pattern match using regular expressions

Hi All,

I am fairly new to Teradata 14 and want to use the Regular Expressions.

I have a column that has values as below.

COL_A
-------------------
<ABC:+15419928832>
<ABC:5419928542>

What I need to do is find out whether this column has a number between 5419928000 - 5419928999.

That is ... whether 5419928832 and 5419928542 are in the range.

How should I do it using regular expressions?

Thanks,

Aarsh

Tags (2)
4 REPLIES
Senior Apprentice

Re: String pattern match using regular expressions

Hi Aarsh,

 WHERE CAST(REGEXP_SUBSTR(col, '[0-9]+') AS BIGINT) BETWEEN 5419928000 AND 5419928999

If you care about a leading sign you can add it to the regex: '[+-]?[0-9]+'

Enthusiast

Re: String pattern match using regular expressions

Hi Dieter,

Thank you very much for your reply.

However, what I want is that only the 10 digit number be considered whether +1 is there or not.

The regular expression you gave gives me the second record, but I want both the records.

Really appreciate your help here. :)

Enthusiast

Re: String pattern match using regular expressions

I hope I can read Dieter's letter, since it is open :). Hope he does not mind.

How about  SUBSTR(CAST(REGEXP_SUBSTR('<ABC:5419928542>', '[0-9]+') AS integer),-10,10)

 or bigint

Senior Apprentice

Re: String pattern match using regular expressions

Hi Aarsh,

you should clearly specifiy what you want. In your first example there's a number 15419928832 which is not within the expected range :-)

So you need exactly 10 digits, are you dealing with US phone numbers?

Is there always a ">" after the number? 

CAST(REGEXP_SUBSTR(col, '([0-9]{10})(?=>)') AS BIGINT)

I can't implement a regex to extract the last 10 digits (there probably is one, but I only have some basic skills), you might combine a regex and MOD:

CAST(REGEXP_SUBSTR(col, '([0-9]{10,})') AS BIGINT) MOD 10000000000