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

CodePudding user response：

Just try one of these ways:

1. Solution 1:

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

2. 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.

CodePudding user response：

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

CodePudding user response：

The REPLACE function can do this:

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

sql editor online