Removing '&' character from a string in Oracle 11g?

Question

How can I "REMOVE" &' character from a string in Oracle 11g? I tried the following by I receive a prompt that asks me 'AMP' (Enter Substitution Value) value... I don't know what's the problem. Most of the questions I found out (including one which is marked duplicate of this question) are talking about escaping the ampersand. In my situation it is a field which keeps the URL of the certain reviews and it has "&AMP;" with it which needed to be removed. Here is the code:

  UPDATE table_name
  set name=REPLACE(name, "&amp;", '&')

Please let me know if this is not the right way, then what is? Help is much appreciated.

Edited my question: it is to remove '&' not escape. – bigZbuzzzinga Mar 30 '15 at 17:11 — bigZbuzzzinga, Mar 30 '15 at 17:11

score 0 · Answer 1 · answered Mar 28 '15 at 06:37

0

Use this... Replace(name, chr (38)||'amp', '') and for like also.

answered Mar 28 '15 at 06:37

Vinish Kapoor

669
1
7
13

38 is ascii code of &.... chr function will convert this to & at the time of exec.. hence it will not conflict. – Vinish Kapoor Mar 30 '15 at 17:18
Thanks Vinish! It helped me a lot. It wasn't exactly the same code that you suggested by I had to tweak it a bit. – bigZbuzzzinga Mar 31 '15 at 16:00

Removing '&' character from a string in Oracle 11g?

1 Answers1