sql to replace two strings in one column

Question

I want to replace two characters in a single string.

Replace

Name 
I am not aware of any or potential that hasn’t yet been reported

with

Name 
I am not aware of any/potential that hasn't yet been reported

I've used this query:

replace(Replace(Name, ' or ', '/'), '’', ''')

But I get an error

ORA-01756: quoted string not properly terminated

this is an apostrophe not a single quote. And i tried adding quotes around it one more but didnt work — SSA_Tech124, Apr 23 '22 at 05:05
It isn't complaining about the "smart" quote, it's failing because of the imbalanced single quote `'` on the end. Fix that and it works correctly `replace(Replace(Name,' or ','/'),'’','''')` . https://dbfiddle.uk/?rdbms=oracle_21&fiddle=fe2e8a5b108cb893f15b0dd2c6af8263 — SOS, Apr 23 '22 at 05:10

Jonas Metzler · Accepted Answer · 2022-04-23T05:24:05.417

1

I don't really understand what you ary trying to do with your double replace. You can just use one:

SELECT REPLACE(name,' or ','/') newstring FROM yourstrings;

If you really need a double replace, you can do this, too:

SELECT REPLACE(REPLACE(name,' or ','/'),'’','''') newstring FROM yourstrings;

More interesting is how to insert such strings which contain apostrophes, quotes etc. You can do it using the q operator, as example:

INSERT INTO yourstrings VALUES 
(q'[I am not aware of any or potential that hasn’t yet been reported ]');

Please see this working example: db<>fiddle

edited Apr 23 '22 at 05:24

answered Apr 23 '22 at 05:10

Jonas Metzler

In the first statment you are just replacing "or". I want to replace both or and "'" – SSA_Tech124 Apr 23 '22 at 05:12
If you see the first row there is "hasn’t" aporstrophe not single quotes – SSA_Tech124 Apr 23 '22 at 05:13
@JonasMetzler - If you look closer, it's not a single quote .. it's some sort of "smart" quote: `hasn’t` not `hasn't`. Though this same question has already been asked and answered *many* times .... – SOS Apr 23 '22 at 05:13
Ok, I edited the answer. In the question you said you want to replace two characters, but you wanted to replace 3 ("or" are already 2) ;) – Jonas Metzler Apr 23 '22 at 05:17

score 0 · Answer 2 · answered Apr 23 '22 at 05:14

0

select replace(replace
('I am not aware of any or potential that hasn’t yet been reported',' or ','/'),
'’','''') 
from dual;

Refer solution here

answered Apr 23 '22 at 05:14

Pankaj

2 Answers2