All Forums Database
aarsh.dave 24 posts Joined 11/12
22 May 2014
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

dnoeth 4628 posts Joined 11/04
22 May 2014

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]+'

Dieter

aarsh.dave 24 posts Joined 11/12
22 May 2014

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

Raja_KT 1246 posts Joined 07/09
22 May 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
22 May 2014

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

 

Dieter

You must sign in to leave a comment.