Oracle sql replace multiple special character with another in single query

Question

How can I replace a particular special character with another character?

Eg. In the sentence I want to replace à with a and è with e I want to replace à with a and è with e both in same query.

If I use replace simply it doesn't work in SQL Developer 21.2.

Oraccle database: 12 c

I took dump of the problem character:

select dump('é', 1016) from dual

Result:

Typ=96 Len=2 CharacterSet=AL32UTF8: c3,a9

"Doesn't work" provides absolutely no information about your real issue. Please clarify what it means and provide error codes/messages or result you want and result you get along with your current code. — astentx
– astentx, Commented Oct 11, 2022 at 20:19

Reza Davoudian · Accepted Answer · 2022-10-11 11:59:09Z

2

Just try one of these ways:

Solution 1:

SELECT REPLACE( REPLACE('I want to replace à with a and è with e', 'à', 'a'),'è', 'e') FROM DUAL;

Solution 2:

SELECT translate('I want to replace à with a and è with e', 'àè', 'ae') FROM DUAL;

the second looks like better.

I use Oracle 12C release 2.

answered Oct 11, 2022 at 11:59

Reza Davoudian

1636 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Velocity Over a year ago

Unfortunately it doesn't work. Sql developer version is 21.2, could this be environment issue?

Jubin Justifies Over a year ago

Well, it might be, I tested this in Oracle 21c as well. What error are you getting @Velocity ?

Velocity Over a year ago

No error. It just doesn't replace. No change after running the query.

Thorsten Kettner Over a year ago

@Velocity: You are saying that both queries return the original string I want to replace à with a and è with e? That is hard to believe. I tried this with Oracle 11.2, and it worked. I tried this with SQL Developer 22.2, and it worked. It is very unlikely that some older Oracle version fails with these simple queries. And it is very unlikely that some older SQL Developer version somehow prevents Oracle from returning the proper result. Have you tried exact the two queries that Reza is showing?

Velocity Over a year ago

yes, i used exct query.

|

Jubin Justifies · Accepted Answer · 2022-10-12 09:01:08Z

1

You can achieve this using TRANSLATE() function like this :

edited Oct 12, 2022 at 9:01

Jubin Justifies

4015 silver badges12 bronze badges

answered Oct 11, 2022 at 12:06

Arbaaz Ali

112 bronze badges

Comments

Slava Rozhnev · Accepted Answer · 2022-10-11 11:38:14Z

0

The REPLACE function can do this:

SELECT REPLACE( 'I want to replace à with a', 'à', 'a') FROM DUAL;

sql editor online

answered Oct 11, 2022 at 11:38

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

Oracle sql replace multiple special character with another in single query

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related