Question: Table Details - Has 2 columns `ID` and `MSG_INFO`

```
ID - 1; MSG_INFO Party is carrying gold in a car which is made of gold

ID - 2; MSG_INFO Party is carrying whitegold in a car which made of gold

```

I want to write a query in which if I search for gold in the MSG\_INFO column and if there is an exact match then I append ~ to gold and no action to be performed when there is no exact match.

Expected output

```
ID -1 ; MSG_INFO Party is carrying ~gold in a car which is made of ~gold
ID -2 ; MSG_INFO Party is carrying whitegold in a car which made of ~gold

```

Answer: 
In Oracle `REGEXP`, there is no `\b` pattern to match word boundaries. A commonly used solution looks something like this.

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

```

[**DEMO**](http://sqlfiddle.com/#!4/60576c/3)

This searches for the word gold at the start of the string, surrounded by a space on either side, end of string, or a non-word character (such as ? or -). `\1,\2,\3` represent the characters matched within the 1st,2nd and 3rd parentheses.
You can use the REPLACE function. Sample query below.

```
SELECT REPLACE('gold a gold a  agold  ggolg', ' gold ', '~gold')
  FROM dual;

```

In your case, the query could be,

```
SELECT id, REPLACE(msg_info, ' gold ', '~gold')
  FROM your_table;

```

Note: In the second argument, you can put spaces or not, it depends on you. Also, you can look for more details of the REPLACE function in the internet.