REGEXP_REPLACE for last occurrence only

Database
Enthusiast

REGEXP_REPLACE for last occurrence only

Is there a way to have the occurence option start at the end of the string and work backward, so that I can replace the 1st occurence from the end of the string? I have some strings where there is normally a state abbreviation, preceeded by two spaces at the end. All I am trying to do is strip them out by replacing with ''. The problem with starting from the beginning is that there can be any range of occurrences from 1-5+ that match two whitespace characters following by two word characters, so that I can't specify the position argument at the end. A few of the strings would look something similar to this:

 

'<word1>  <word2>        <word3>  MN'

'<word1>  <word2>  <word3>        <word4>  CA'

'<word1>        <word2>  CA'

 

I'm not a RegEx expert by any means, so perhaps this is more of a regex problem than a problem with the occurence argument. However, I'm open to any suggestions 

Tags (3)

Accepted Solutions
Teradata Employee

Re: REGEXP_REPLACE for last occurrence only

If you want to match only at the end of the string, use the $ anchor in the pattern: '\s+\w\w$'

1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

Re: REGEXP_REPLACE for last occurrence only

If you want to match only at the end of the string, use the $ anchor in the pattern: '\s+\w\w$'

Enthusiast

Re: REGEXP_REPLACE for last occurrence only

Teradata Regular Expressions and Examples

 

http://dwgeek.com/teradata-regular-expressions-examples.html/

DARORA
Enthusiast

Re: REGEXP_REPLACE for last occurrence only

Hi,

I'm using this function to remove special characters. i want to know the types of special characters? i mean what kind of special characters it will remove? can anyone please provide the list of special characters?

In below statement i know the meaning for ' ',1,0, 'i' this statement. But, i'm trying to understand the use for  '[\t\r\n\v\f|]' and '[^[:print:]]' these two functions. 

TRIM(REGEXP_REPLACE(REGEXP_REPLACE(CAST(INOUT_STRING AS CHAR(3000)), '[\t\r\n\v\f|]',' ',1,0, 'i'), '[^[:print:]]','',1,0, ''))

 

Can anyone pleasae help me on this.

Thanks.

Enthusiast

Re: REGEXP_REPLACE for last occurrence only

https://analytics.ncsu.edu/sesug/2016/DM-224_Final_PDF.pdf

This pdf will clear your doubts.

DARORA
Highlighted
Enthusiast

Re: REGEXP_REPLACE for last occurrence only

Thanks a lot deepak. This would be really helpful for me. 

Enthusiast

Re: REGEXP_REPLACE for last occurrence only

Thanks fred. This would be really helpful for me.