RegExp Find and Replace ignoring part matches

Question

I have a string formatted like:

'(val$1,val$1,val$1,val$2,val$3,val$4,val$5,val$6,val$7,val$8,val$9,val$10,val$11,val$12)'

and what I am trying to do is replace val$1 with some text e.g. XYZ

Matching on just val$1 produces:

'(XYZ,XYZ,XYZ,val$2,val$3,val$4,val$5,val$6,val$7,val$8,val$9,XYZ0,XYZ1,XYZ2)'

If anyone fancies giving me a hand, I would love to know what reg exp would ignore the ones at the end so the resulting string would look like:

'(XYZ,XYZ,XYZ,val$2,val$3,val$4,val$5,val$6,val$7,val$8,val$9,val$10,val$11,val$12)'

Something like `\bval\$1\b` https://regex101.com/r/7bO9ar/1/ — adiga, Mar 07 '19 at 17:27
Thanks for that. Outside of Oracle, that is looking good by I am using their REGEXP_REPLACE and \b is not supported. Is there another way around this? — dunkyduncs, Mar 07 '19 at 18:13
I'm not familiar with that. You should add those relevant tags to the question to reach the correct *audience* :) — adiga, Mar 07 '19 at 18:17
If word boundaries aren't supported, you can try `val\$1(,|\))` This only matches substrings with `,` or `)` after `val$1` — adiga, Mar 07 '19 at 18:26

Barbaros Özhan · Accepted Answer · 2019-03-08T06:20:05.197

0

You can add an extra digit by \D to the pattern for regexp_replace

select regexp_replace('(val$1,val$1,val$1,val$2,val$3,val$4,val$5,val$6,val$7,val$8,val$9,
                        val$10,val$11,val$12)','\val\$1(\D)','XYZ\1') as "Result"
  from dual;

Result
----------------------------------------------------------------------------------
(XYZ,XYZ,XYZ,val$2,val$3,val$4,val$5,val$6,val$7,val$8,val$9,val$10,val$11,val$12)

edited Mar 08 '19 at 06:20

answered Mar 07 '19 at 19:20

Barbaros Özhan

59,113
10
31
55

@dunkyduncs and Barbaros. This puts an extra comma at the end if there's a `val$1` at the end , i.e just before `)`. Try `REGEXP_REPLACE(string, ','\val\$1(\D)','XYZ\1')` instead – Kaushik Nayak Mar 08 '19 at 05:16
..or even better `[^0-9a-zA-Z]` instead of `\D` – Kaushik Nayak Mar 08 '19 at 05:22
@KaushikNayak yes, your solution is more generalized and better, thanks.Depending on mine, I preferred the first one. – Barbaros Özhan Mar 08 '19 at 06:20
Thanks. What do the the brackets do in this instance \val\$1([^0-9a-zA-Z]) – dunkyduncs Mar 08 '19 at 08:43
@dunkyduncs welcome, yes this works, too. But I think no need to include all these pattern elements verbosely. – Barbaros Özhan Mar 08 '19 at 08:45
`SELECT regexp_replace( '(val$1,val$1,val$1,val$2,P0_COY,val$3,val$4,P0_COY2,val$5,P0_COY_2,val$6,val$7,val$8,val$9,val$10,val$11,val$12)' , '\P0_COY(\D)' , '''XYZ''\1' , 1, 0, 'i') as tester FROM dual;` `(val$1,val$1,val$1,val$2,'XYZ',val$3,val$4,P0_COY2,val$5,'XYZ'_2,val$6,val$7,val$8,val$9,val$10,val$11,val$12)` How can I deal with the _ character so that the result set look like: `(val$1,val$1,val$1,val$2,'XYZ',val$3,val$4,P0_COY2,val$5,P0_COY_2,val$6,val$7,val$8,val$9,val$10,val$11,val$12)` – dunkyduncs Mar 08 '19 at 11:43
Think I have solved it: ([^0-9a-z_]) in place of (\D) – dunkyduncs Mar 08 '19 at 12:26

Littlefoot · Answer 2 · 2019-03-07T21:02:26.983

0

Do you really need regular expressions for that? Looks like a simple task for the good, old REPLACE function. If there's a lot of data you have to deal with, regex will probably be way slower.

SQL> select replace('(val$1,val$1,val$1,val$2,val$3,val$4,val$5,val$6,val$7,val$8,val$9,val$10,val$11,val$12)',
  2                 'val$1,', 'xyz,') result
  3  from dual;

RESULT
----------------------------------------------------------------------------------
(xyz,xyz,xyz,val$2,val$3,val$4,val$5,val$6,val$7,val$8,val$9,val$10,val$11,val$12)

SQL>

edited Mar 07 '19 at 21:02

answered Mar 07 '19 at 19:44

Littlefoot

131,892
15
35
57

Not the desired result set, so yes, a reg exp is needed. See above for the desired output. Cheers, Duncs – dunkyduncs Mar 07 '19 at 20:57
Ah! Sorry, didn't notice the end of the string. Anyway, REPLACE still does the job. I modified code I previously posted, have a look. – Littlefoot Mar 07 '19 at 21:02
Which *minor issue* is it? – Littlefoot Mar 08 '19 at 11:08

score 0 · Answer 3 · answered Mar 08 '19 at 13:44

0

You could also search for "val$1" where followed by a comma or a closing paren (if it happens to be at the end of the string).

'val\$1(,|\\)', 'XYZ\1'

answered Mar 08 '19 at 13:44

Gary_W

9,933
1
22
40

RegExp Find and Replace ignoring part matches

3 Answers3