REGEXP_SUBSTR TD15.10 - Retrieve GROUP 2 only

Database
Highlighted
Enthusiast

REGEXP_SUBSTR TD15.10 - Retrieve GROUP 2 only

Hello,

(TD 15.10 REGEXP_SUBSTR HELP)

 

I have a column with comma separated string values in following format:

 

1,9999999999999999,7775555555,P,9045,8775555555,0b115ttt53m049,002l,,N,,IAA0,3399
1,8888888888888888,4085555555,P,9027,8665555555,0c115ttt5fn44f,0t2a,,N,,COA0,3399
1,7777777777777777,3035555555,F,9045,8005555555,0d115ttt5oba09,000c,,N,A0,KTA0,3399

 

I need the 14 length character that starts with 0. These strings do not always start with 0, but are always 14 characters long. Also there is always 6 commas before this value. 

 

0b115ttt53a049
0c115ttt5fc44f
0d115ttt54ba09

 

I have achieved grabbing this string with following REGEX. However, the characters are coming with the front and end COMMAS.  I need to know how to retrieve GROUP 2 of the regex expression. According to regex101.com, GROUP 2 would provide the 14 characters WITHOUT commas.

 

REGEXP_SUBSTR (COLUMN, '\,(([0-9]|[a-zA-Z]){14})\,',1,1,'i'))

or

(REGEXP_SUBSTR (COLUMN, '\,(([0-9]|[a-zA-Z]){14})\,'))

 

Another recommendation I've gotten is to retrieve the 14 characters between the 6th and 7th COMMA. I don't use regular expressions much so that has been a bigger struggle. If easy enough for someone to recommend I'd appreciate it, otherwise, just retrieving GROUP 2 would be great. 

 

Thank you!

 

 


Accepted Solutions
Junior Contributor

Re: REGEXP_SUBSTR TD15.10 - Retrieve GROUP 2 only

There's no 6th parameter for REGEXP_SUBSTR, the function is named REGEXP_SUBSTR_GPL instead (don't know why).

 

There are two possible ways to get rid of the commas:

 

Don't add it to the matching group using lookbehind/lookahead:

 

RegExp_Substr (COLUMN, '(?<=\,)(([0-9]|[a-zA-Z]){14})(?=\,)',1,1,'i')

 

 

Apply TRIM:

trim(both ',' from REGEXP_SUBSTR (COLUMN, '\,(([0-9]|[a-zA-Z]){14})\,',1,1,'i')))

 

 

Tags (1)
1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

Re: REGEXP_SUBSTR TD15.10 - Retrieve GROUP 2 only

You could try:

(REGEXP_SUBSTR (COLUMN, '([^,]*,){6}([0-9A-Za-z]{14}).*', 1,1,'i', 2)

Enthusiast

Re: REGEXP_SUBSTR TD15.10 - Retrieve GROUP 2 only

Thank you for the prompt reply! 

 

When I run that code I receive [9881] Function 'REGEXP_SUBSTR' called with an invalid number or type of parameters. I had seen on a previous post that the last trailing with 1,1,'i', 2 would provide group 2 but I just get that error. 

 

I can see in https://regex101.com that ([^,]*,){6}([0-9A-Za-z]{14}).*  does exactly what I had asked for so thank you for the expression! 

 

Any idea on the TD error? 

Teradata Employee

Re: REGEXP_SUBSTR TD15.10 - Retrieve GROUP 2 only

I guess regex_substr in your release of Teradata doesn't support that 6th parameter.  So you could nest in regex_replace, such as:

REGEXP_SUBSTR ( REGEXP_REPLACE ( COLUMN, '([^,]*,){6}' ), ([0-9A-Za-z]{14})' )

Note that there is no replace-string argument in the replace function, so this should drop the first 6 ".*," occurrences.

Alternatively, if this is always the 7th value, then REGEXP_SUBSTR( COLUMN, '[^,]*,', 1, 7 ) would return the 7th occurrence, but you'd still have to drop the trailing comma:

OREPLACE( REGEXP_SUBSTR( COLUMN, '[^,]*,', 1, 7 ), ',' )

(I'm assuming OREPLACE is a little more efficient than REGEXP_REPLACE.)

Junior Contributor

Re: REGEXP_SUBSTR TD15.10 - Retrieve GROUP 2 only

There's no 6th parameter for REGEXP_SUBSTR, the function is named REGEXP_SUBSTR_GPL instead (don't know why).

 

There are two possible ways to get rid of the commas:

 

Don't add it to the matching group using lookbehind/lookahead:

 

RegExp_Substr (COLUMN, '(?<=\,)(([0-9]|[a-zA-Z]){14})(?=\,)',1,1,'i')

 

 

Apply TRIM:

trim(both ',' from REGEXP_SUBSTR (COLUMN, '\,(([0-9]|[a-zA-Z]){14})\,',1,1,'i')))

 

 

Tags (1)
Enthusiast

Re: REGEXP_SUBSTR TD15.10 - Retrieve GROUP 2 only

Thanks again! 

 

REGEXP_SUBSTR ( REGEXP_REPLACE ( COLUMN, '([^,]*,){6}' ), ([0-9A-Za-z]{14})' ) 

Gave me [9881] Function 'REGEXP_SUBSTR' called with an invalid number or type of parameters

 

OREPLACE( REGEXP_SUBSTR( COLUMN, '[^,]*,', 1, 7 ), ',' )

Worked when the string was full expected length but retrieved other values when full string was less characters (in these cases, there's still the same number of commas, so not sure why it'd happen)

 

Will continue moidifying and testing these solutions, thank you for your support!

Enthusiast

Re: REGEXP_SUBSTR TD15.10 - Retrieve GROUP 2 only

Hello Dnoeth,

 

Thank you for your reply!  I did see REGEXP_SUBSTR_GPL on one of your previous posts but my SQL ASSISTANT is not recognizing this as a function.

 

Nonetheless,

Both of the expressions you provided to get rid of the commas helped accomplish this, thank you!