SQL 如何在oracle的varchar2列中替换重音字母
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28837263/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how replace accented letter in a varchar2 column in oracle
提问by GSandro_Strongs
I have a varchar2 column named NAME_USER. for example the data is: JUAN ROM?N but I try to show JUAN ROMAN, replace á to A in my statement results. How Can I do that?. Thanks in advance.
我有一个名为 NAME_USER 的 varchar2 列。例如，数据是：JUAN ROM?N 但我尝试显示 JUAN ROMAN，在我的语句结果中将 á 替换为 A。我怎样才能做到这一点？。提前致谢。
回答by Ankur Bhutani
Use convertfunction with the appropriate charset
使用具有适当字符集的转换函数
select CONVERT('JUAN ROM?N', 'US7ASCII') from dual;
below are the charset which can be used in oracle:
以下是可在 oracle 中使用的字符集：
US7ASCII: US 7-bit ASCII character set
WE8DEC: West European 8-bit character set
WE8HP: HP West European Laserjet 8-bit character set
F7DEC: DEC French 7-bit character set
WE8EBCDIC500: IBM West European EBCDIC Code Page 500
WE8PC850: IBM PC Code Page 850
WE8ISO8859P1: ISO 8859-1 West European 8-bit character set
回答by Bacs
You could use replace, regexp_replaceor translate, but they would each require you to map all possible accented characters to their unaccented versions.
您可以使用replace,regexp_replace或translate，但它们都需要您将所有可能的重音字符映射到它们的非重音版本。
Alternatively, there's a function called nlssort()which is typically used to override the default language settings used for the order byclause. It has an option for accent-insensitive sorting, which can be creatively misused to solve your problem. nlssort()returns a binary, so you have to convert back to varchar2 using utl_raw.cast_to_varchar2():
或者，有一个被调用的函数nlssort()，它通常用于覆盖用于order by子句的默认语言设置。它有一个不区分重音的排序选项，可以创造性地误用它来解决您的问题。nlssort()返回一个二进制文件，因此您必须使用utl_raw.cast_to_varchar2()以下命令将其转换回 varchar2 ：
select utl_raw.cast_to_varchar2(nlssort(NAME_USER, 'nls_sort=binary_ai'))
from YOUR_TABLE;
Try this, for a list of accented characters from the extended ASCII set, together with their derived, unaccented values:
试试这个，获取扩展 ASCII 集中的重音字符列表，以及它们派生的非重音值：
select level+192 ascii_code,
chr(level+192) accented,
utl_raw.cast_to_varchar2(nlssort(chr(level+192),'nls_sort=binary_ai')) unaccented
from dual
connect by level <= 63
order by 1;
Not really my answer - I've used this before and it seemed to work ok, but have to credit this post: https://community.oracle.com/thread/1117030
不是我的答案 - 我以前用过这个，它似乎工作正常，但必须归功于这篇文章：https: //community.oracle.com/thread/1117030
ETA: nlssort()can't do accent-insensitive without also doing case-insensitive, so this solution will always convert to lower case. Enclosing the expression above in upper()will of course get your example value back to "JUAN ROMAN". If your values can be mixed case, and you need to preserve the case of each character, and initcap()isn't flexible enough, then you'll need to write a bit of PL/SQL.
ETA：nlssort()不能在不区分大小写的情况下不区分重音，因此此解决方案将始终转换为小写。将上面的表达式括起来upper()当然会使您的示例值返回“JUAN ROMAN”。如果您的值可以混合大小写，并且您需要保留每个字符的大小写，并且initcap()不够灵活，那么您需要编写一些 PL/SQL。
回答by Ditto
select replace('JUAN ROM?N','?','A')
from dual;
If you have more mappings to make, you could use TRANSLATE ...
如果您要进行更多映射，则可以使用 TRANSLATE ...
回答by Isaac
You can use regular expressions:
您可以使用正则表达式：
SELECT regexp_replace('JUAN ROM?Ní','[[=A=]]+','A' )
FROM dual;
