Home » SQL & PL/SQL » SQL & PL/SQL » Replace Accented Characters
Replace Accented Characters [message #654080] Wed, 27 July 2016 00:39 Go to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
Hi ,
Any one can help me with replacing Accented Characters with its base characters.

Please find the Example Below
SELECT regexp_replace('ñaàeéêèioô','[[=n=]]','n' )
FROM dual;

Output:
naàeéêèioô


In the above Example i have replace ñ as n. Similar way i want replace the rest of the characters as well

Thanks in Advance
Re: Replace Accented Characters [message #654084 is a reply to message #654080] Wed, 27 July 2016 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 66048
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use TRANSLATE and list all characters and its conversion.

Re: Replace Accented Characters [message #654085 is a reply to message #654080] Wed, 27 July 2016 01:02 Go to previous messageGo to next message
Frank Naude
Messages: 4557
Registered: April 1998
Senior Member
Try with translate():
SELECT translate('ñaàeéêèioô','ñàéêèô','naeeeo') FROM dual;
Re: Replace Accented Characters [message #654089 is a reply to message #654085] Wed, 27 July 2016 01:12 Go to previous messageGo to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
Thanks For your Suggestion. But how will i mention some unknown accented characters if i received from particular column.

For Example ú, ü

[Updated on: Wed, 27 July 2016 01:12]

Report message to a moderator

Re: Replace Accented Characters [message #654094 is a reply to message #654089] Wed, 27 July 2016 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 66048
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

All accented characters are known, just list them all in second parameter of TRANSLATE and its conversion in 3rd one.
Please refer to the documentation of this function.

Re: Replace Accented Characters [message #654098 is a reply to message #654094] Wed, 27 July 2016 01:48 Go to previous messageGo to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
Is there any other way that we can handle using regexp_replace??
Re: Replace Accented Characters [message #654104 is a reply to message #654098] Wed, 27 July 2016 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66048
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No and regexp_replace would be anyway fast slower than translate and you will have anyway to list all characters and their translation.

Re: Replace Accented Characters [message #654108 is a reply to message #654104] Wed, 27 July 2016 02:22 Go to previous messageGo to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
"Tilded" don't seem to work that way, but one method for other accents could be to convert them to ascii:

SQL> SELECT CONVERT('ñaàeéêèioô', 'US7ASCII' ) FROM dual;

CONVERT('ÑAÀEÉÊÈIOÔ','US7ASCII')
----------------------------------------
?aaeeeeioo


Re: Replace Accented Characters [message #654114 is a reply to message #654104] Wed, 27 July 2016 03:23 Go to previous messageGo to next message
Littlefoot
Messages: 21446
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel

... would be anyway fast slower than

/forum/fa/10057/0/
Re: Replace Accented Characters [message #654126 is a reply to message #654114] Wed, 27 July 2016 04:51 Go to previous messageGo to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
How can we identify all Accented Charecters??
Re: Replace Accented Characters [message #654128 is a reply to message #654126] Wed, 27 July 2016 04:57 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
Here's a list of all characters
select distinct chr(level)
from dual
connect by level <= 256
You choose which ones are in your requirements
Re: Replace Accented Characters [message #654198 is a reply to message #654080] Thu, 28 July 2016 04:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8904
Registered: November 2002
Location: California, USA
Senior Member
If you don't care about case, you can use the following. In addition to being accent-insensitive, binary_ai is also case-insensitive, so it converts everything to lower case. Of course you can always wrap upper or initcap around it.

SCOTT@orcl_12.1.0.2.0> select utl_raw.cast_to_varchar2(nlssort('ñaàeéêèioô', 'nls_sort=binary_ai')) from dual
2 /

UTL_RAW.CAST_TO_VARCHAR2(NLSSORT('ÑAÀEÉÊÈIOÔ','NLS_SORT=BINARY_AI'))
--------------------------------------------------------------------------------
naaeeeeioo

1 row selected.
Re: Replace Accented Characters [message #654434 is a reply to message #654198] Tue, 02 August 2016 23:42 Go to previous messageGo to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
Hi,
How can i translate Trademark symbol to some other Character. ™ should be replaced as TM.

select translate('Target™','™','TM') from dual


Required Output
===============
TargetTM

Re: Replace Accented Characters [message #654436 is a reply to message #654434] Wed, 03 August 2016 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66048
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use REPLACE.
How did you handle the previous question?
Post your solution.

Re: Replace Accented Characters [message #654438 is a reply to message #654434] Wed, 03 August 2016 01:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8904
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> SET UNDERLINE '='
SCOTT@orcl_12.1.0.2.0> COLUMN "Required Output" FORMAT A15
SCOTT@orcl_12.1.0.2.0> SELECT REPLACE ('Target™', '™', 'TM') AS "Required Output" FROM DUAL;

Required Output
===============
TargetTM

1 row selected.
Re: Replace Accented Characters [message #654439 is a reply to message #654436] Wed, 03 August 2016 01:31 Go to previous messageGo to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
I have used the below query

select replace('Target™',chr(ascii('™')),'TM') from dual

But i want to replace Trademark symbol with TM & all accented Characters with its base characters. can we do it by storing all special characters & its replacement characters in a table. Then use this table in case of any replacement for a special characters?
Re: Replace Accented Characters [message #654442 is a reply to message #654439] Wed, 03 August 2016 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 66048
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you say you use REPLACE for the previous question about accented characters? And this works? I doubt unless you used one REPLACE per character.

Re: Replace Accented Characters [message #654443 is a reply to message #654439] Wed, 03 August 2016 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 66048
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
can we do it by storing all special characters & its replacement characters in a table. Then use this table in case of any replacement for a special characters?
Yes but why doing this? The list is static why to use a table for this?

Re: Replace Accented Characters [message #654446 is a reply to message #654443] Wed, 03 August 2016 02:43 Go to previous messageGo to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Michel Cadot wrote on Wed, 03 August 2016 08:38
Yes but why doing this? The list is static why to use a table for this?
Depends. If he wants to replace all the emoji that find their way into Unicode with their "basic characters", too, then it might get quite complicated.

That ™ is TM is quite easy, but what about 🎄 ?

[Updated on: Wed, 03 August 2016 02:44]

Report message to a moderator

Re: Replace Accented Characters [message #654448 is a reply to message #654446] Wed, 03 August 2016 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 66048
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This does not change the fact that the list is static once you have chosen the replacement characters you want.

Re: Replace Accented Characters [message #654466 is a reply to message #654448] Wed, 03 August 2016 05:08 Go to previous messageGo to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
I want to maintain these special characters in a seperate static table just to avoid hardcoding the values in my code.
Re: Replace Accented Characters [message #654467 is a reply to message #654466] Wed, 03 August 2016 05:12 Go to previous message
Michel Cadot
Messages: 66048
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why? To have bad performances?
What is the difference between hard coded in code or hard coded in table?

Previous Topic: Execution of Query
Next Topic: Make use of UNUSABLE_INDEX by uisnh HInt
Goto Forum:
  


Current Time: Tue Dec 11 14:45:42 CST 2018