Reg Exp - insert a special character (~) between the first and the third character and only when an exact match is present

Question

My table has a column MSG_INFO with sample data:

Party is carrying gold in a car which is made of ,gold,
Party is carrying whitegold in a car which made of gold

I need a SQL expression that searches for gold and if there is an exact match then I append ~ after the first character of the exact match gold and no action to be performed when there is no exact match.

gold can only be surrounded by space or comma on either side.

Expected output:

Party is carrying g~old in a car which is made of ,g~old,
Party is carrying whitegold in a car which made of g~old

Note - After inserting ~ space before and after gold should still be preserved.

This is what I've started with:

SELECT REGEXP_REPLACE (msg_info, '(^|\s|\W)(gold)($|\s|\W)', '\1~\2\3', 1,0,'i')
FROM   table;

Party is carrying ~gold in a car which is made of ,~gold,
Party is carrying whitegold in a car which made of ~gold

It correctly identifies the correct instances of gold but it puts the ~ before the word rather than after the first character.

Background question: https://stackoverflow.com/questions/47973053/how-to-append-a-special-character-in-oracle-sql-when-an-exact-match-is-found-in — Jeffrey Kemp, Feb 07 '18 at 05:14

score 1 · Answer 1 · answered Feb 07 '18 at 05:22

1

Split (gold) into two patterns: (g)(old) in the search string, e.g.:

select regexp_replace('Party is carrying gold in a car which is made of ,gold,',
        '(^|\s|\W)(g)(old)($|\s|\W)', '\1\2~\3\4', 1,0,'i') from dual
union all
select regexp_replace('Party is carrying whitegold in a car which made of gold',
        '(^|\s|\W)(g)(old)($|\s|\W)', '\1\2~\3\4', 1,0,'i') from dual;


Party is carrying g~old in a car which is made of ,g~old,
Party is carrying whitegold in a car which made of g~old

answered Feb 07 '18 at 05:22

Jeffrey Kemp

59,135
14
106
158

http://sqlfiddle.com/#!4/0160d/1 - Could you check the example , I tried modifying your query. It is not working as I expect. Is there something I am missing. – user3442679 Feb 07 '18 at 06:08
forget it , I know what the mistake is. The quotes was in the wrong place. Will confirm with another round of testing. – user3442679 Feb 07 '18 at 06:15
why are we introducing two patterns instead of one word to be searched, is it only for inserting the ~ we are splitting it up. – user3442679 Feb 07 '18 at 06:19
@user3442679 : If you don't need to match the case and simply want to replace `gold` with `g~old`, then splitting would not be required, we could simply use `'\1g~old\3'` as replacement pattern. – Kaushik Nayak Feb 07 '18 at 06:41

Albin Philip · Answer 2 · 2018-02-07T06:21:59.280

0

Try this out, it should work.

REGEX_REPLACE(msg_info,'([\s,](g)(o)(l)(d)[\s,])','\2

edited Feb 07 '18 at 06:21

answered Feb 07 '18 at 05:00

Albin Philip

1
2

score 0 · Answer 3 · answered Feb 07 '18 at 15:38

This regex is a little different in that it uses nested groups. Don't forget "Gold" at the start of the line.

SQL> with tbl(msg_info) as (
      select 'Party is carrying gold in a car which is made of ,gold,' from dual union all
      select 'Party is carrying whitegold in a car which made of gold' from dual union all
      select 'Gold Party gold is carrying whitegold gold goldstar gold' from dual
    )
    select regexp_replace(msg_info, '(( |,|^)g)(old( |,|$))', '\1~\3', 1, 0, 'i') after
    from tbl;

AFTER
--------------------------------------------------------------------------------

Party is carrying g~old in a car which is made of ,g~old,
Party is carrying whitegold in a car which made of g~old
G~old Party g~old is carrying whitegold g~old goldstar g~old

SQL>

Reg Exp - insert a special character (~) between the first and the third character and only when an exact match is present

3 Answers3