Find pattern and replace string between (regexp_replace or substr)

Question

Friends,

I'm trying to generate dynamic sql script and replace string from the original query result.

Original: '/u02/DB/FILE/file01.dbf' or 
Original: '+DATA/DB/FILE/file01.dbf'

Required : '/u01/data/dbname/file01.dbf'

Start search from 2nd character from original, find 1st and 2nd appearance of '/' and replace with data (hopefully this covers both original scenario)
Start search from 2nd character from original, find 2nd and 3rd appearance of '/' and replace with dbname

DB and FILE in original could be any location so can't really hardcode that.

I use below to replace FILE with dbname but that's not helping totally,

Somehow feels approach is too complex and there should be better/diferent way.

    SELECT 'alter database rename file ''' || f.file_name || ''' to '''|| '/u01' ||
          REGEXP_REPLACE(SUBSTR(f.file_name, INSTR(f.file_name, '/',2)), '/FILE/', '/dbname/', 1||''';' stmt
    FROM ( SELECT name file_name, bytes
             FROM v$tempfile
             ORDER BY bytes ESC
         )f;

Thanks and much appreciated.

Try something like regexp_replace('/u02/DB/FILE/file01.dbf', '^([^/]*/[^/]*/)[^/]*/[^/]*', '\1data/dbname'). — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 26, 2017 at 20:30
WOW, that was super quick, looks like it's working as expected, will test completely and confirm. — homer
– homer, Commented May 26, 2017 at 20:39
@WiktorStribiżew it worked perfectly, how can we edit it slightly to search from 2nd character to avoid +DATA? — homer
– homer, Commented May 26, 2017 at 20:49
Sorry, I am not getting that: you should just use ^ to match the start of string, then [^/]* to match 0+ chars other than / and then / - that will be the first /, then add [^/]* to get to the 2nd /. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 26, 2017 at 21:04
Sorry if I was not able to explain it properly, provided solution works if string starts with '/' but if original starts with +DATA then we don't get correct result. so trying to start search of '/' from 2nd character of the original string. — homer
– homer, Commented May 26, 2017 at 21:13

Wiktor Stribiżew · Accepted Answer · 2017-05-26 20:48:48Z

3

You may use a single regex replace operation with:

^([^/]*/[^/]*/)[^/]*/[^/]*

Replace with \1data/dbname.

Details

^ - match start of a string
([^/]*/[^/]*/) - Group 1 (referred to with \1 backreference from the replacement pattern):
- [^/]* - 0+ chars other than /
- / - a slash
- [^/]* - 0+ chars other than /
- / - a slash
[^/]* - 0+ chars other than /
/ - a slash
[^/]* - 0+ chars other than /

A quick online test:

select regexp_replace('/u02/DB/FILE/file01.dbf', '^([^/]*/[^/]*/)[^/]*/[^/]*', '\1data/dbname') AS RESULT from dual

answered May 26, 2017 at 20:48

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Collectives™ on Stack Overflow

Find pattern and replace string between (regexp_replace or substr)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related