I have a string, which represent a file. The file extensions have not the same size. The 2 last characters before the '.' give an important information. Is there a way (probably with regex) to change this 2 characters?

Mystring := blabla || two_characters_to_change || '.' || file_extension

Mystring_concrete_example := 'blabla93.pdf'

select regexp_replace('Mystring_concrete_example','??','39') 

expected result:= 'blaba39.pdf'

does somebody know what should I write instead of '??', in oder to select the 2 characters before the '.' ?

CodePudding user response：

We can use INSTR to find the point and then use SUBSTRING to get the parts that we want to keep.

create table paths(path varchar(100));
insert into paths select 'blabla93.pdf' from dual;

with findings as
(select 
  path,
  INSTR( path, '.') pos
from paths)
select 
  substr(path,1,pos-3) || '22' || substr(path,pos) as newPath
from findings

| NEWPATH      |
| :----------- |
| blabla22.pdf |

db<>fiddle here

CodePudding user response：

If you have extensions that doesn't contain dots (like .tar.gz), then you may search for the dot at the end and extract two previous characters with regex groups:

with a as (
  select 'blabla93.pdf' as file_
  from dual
)
select
  file_
  , regexp_replace(
      file_
      , '(.*)(.{2})(\.[^.] $)'
      , '\1' || 'something' || '\3'
  ) as file_new
from a

FILE_        | FILE_NEW           
:----------- | :------------------
blabla93.pdf | blablasomething.pdf

db<>fiddle here

Explanation:

• (.*) - 1st capturing group, should contain any number * of any characters ..
• (.{2}) - 2nd capturing group, should contain exactly two {2} any . characters
• (\.[^.] $) - 3rd capturing group, should begin with dot \. followed by any positive number of any characters, except dot [^.] and placed at the very end of the string $
• Replacement \N stands for N'th capturing group.

You may also use this online regex tester that contain explanations: https://regex101.com/.