Oracle regex and replace

Question

I have varchar field in the database that contains text. I need to replace every occurrence of a any 2 letter + 8 digits string to a link, such as VA12345678 will return /cs/page.asp?id=VA12345678

I have a regex that replaces the string but how can I replace it with a string where part of it is the string itself?

SELECT REGEXP_REPLACE ('test PI20099742', '[A-Z]{2}[0-9]{8}$', 'link to replace with')
FROM dual;

I can have more than one of these strings in one varchar field and ideally I would like to have them replaced in one statement instead of a loop.

Use backreferences. Google for that word (and Oracle) to see examples. docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm Note though that some letters are not in the A-Z range; for example the letter m (lower case). — user5683823
– user5683823, Commented Mar 16, 2021 at 17:07
You don't need lower case today. This query will still be used two years from now, when the rules will change and you won't be there to help (you will be working in management already). "Defensive coding" anticipates such changes and accounts for them. — user5683823
– user5683823, Commented Mar 16, 2021 at 17:23

EJ Egyed · Accepted Answer · 2021-03-16 17:36:11Z

2

As mathguy had said, you can use backreferences for your use case. Try a query like this one.

SELECT REGEXP_REPLACE ('test PI20099742', '([A-Z]{2}[0-9]{8})', '/cs/page.asp?id=\1')
  FROM DUAL;

edited Mar 16, 2021 at 17:36

answered Mar 16, 2021 at 17:13

EJ Egyed

6,1341 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user5683823 Over a year ago

Why do you have the $ at the end? The OP said specifically he may need to replace several such substrings in a single pass, which means they are not all at the end.

EJ Egyed Over a year ago

I had the $ at the end since OP had it in his original regex. You are correct in the fact that it's not needed if the replacement should be replaced anywhere within the string.

user5683823 · Accepted Answer · 2021-03-16 17:22:04Z

For such cases, you may want to keep the "text to add" somewhere at the top of the query, so that if you ever need to change it, you don't have to hunt for it.

You can do that with a with clause, as shown below. I also put some input data for testing in the with clause, but you should remove that and reference your actual table in your query.

I used the [:alpha:] character class, to match all letters - upper or lower case, accented or not, etc. [A-Z] will work until it doesn't.

with
  text_to_add (link) as (
      select '/cs/page.asp?id=' from dual
    )
, sample_strings (str) as (
      select 'test VA12398403 and PI83048203 to PT3904' from dual
    )
select regexp_replace(str, '([[:alpha:]]{2}\d{8})', link || '\1')
             as str_with_links
from   sample_strings cross join text_to_add
;

STR_WITH_LINKS                                                          
------------------------------------------------------------------------
test /cs/page.asp?id=VA12398403 and /cs/page.asp?id=PI83048203 to PT3904

Collectives™ on Stack Overflow

Oracle regex and replace

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related