笔记：ORACLE数据库表数据全角转半角

说明

数据导入后发现部分数据为全角数据，不符合条件，需要进行转换

分析

使用数据库自带的函数进行处理

语法: TO_MULTI_BYTE(String)

功能: 计算所有单字节字符都替换为等价的多字节字符的String.该函数只有当数据库字符集同时包含多字节
和单字节的字符的时候有效.否则, String不会进行任何处理. TO_MULTI_BYTE和TO_SINGLE_BYTE是相反的
两个函数.

语法: TO_SINGLE_BYTE(String )

功能: 计算String中所有多字节字符都替换为等价的单字节字符.该函数只有当数据库字符集同时包含多字节
和单字节的字符的时候有效.否则, String不会进行任何处理.

处理

update bd_nav_poi 
set name=to_single_byte(to_char(name)),
addname=to_single_byte(to_char(addname)),
othername=to_single_byte(to_char(othername)),
oldname=to_single_byte(to_char(oldname)),
address=to_single_byte(to_char(address)) ;

update bd_nav_poi set 
name=replace(name,'～','~'),
addname=replace(addname,'～','~'),
othername=replace(othername,'～','~'),
oldname=replace(oldname,'～','~'),
address=replace(address,'～','~');

update bd_nav_poi set 
name=replace(name,'＄','$'),
addname=replace(addname,'＄','$'),
othername=replace(othername,'＄','$'),
oldname=replace(oldname,'＄','$'),
address=replace(address,'＄','$');

update bd_nav_poi set 
name=replace(name,'＿','_'),
addname=replace(addname,'＿','_'),
othername=replace(othername,'＿','_'),
oldname=replace(oldname,'＿','_'),
address=replace(address,'＿','_');

update bd_nav_poi set 
name=replace(name,'＝','='),
addname=replace(addname,'＝','='),
othername=replace(othername,'＝','='),
oldname=replace(oldname,'＝','='),
address=replace(address,'＝','=');

update bd_nav_poi set 
name=replace(name,'｛','{'),
addname=replace(addname,'｛','{'),
othername=replace(othername,'｛','{'),
oldname=replace(oldname,'｛','{'),
address=replace(address,'｛','{');

update bd_nav_poi set 
name=replace(name,'｝','}'),
addname=replace(addname,'｝','}'),
othername=replace(othername,'｝','}'),
oldname=replace(oldname,'｝','}'),
address=replace(address,'｝','}'); 


update bd_nav_poi set 
name=replace(name,'＜','<'),
addname=replace(addname,'＜','<'),
othername=replace(othername,'＜','<'),
oldname=replace(oldname,'＜','<'),
address=replace(address,'＜','<');

update bd_nav_poi set 
name=replace(name,'＞','>'),
addname=replace(addname,'＞','>'),
othername=replace(othername,'＞','>'),
oldname=replace(oldname,'＞','>'),
address=replace(address,'＞','>');

update bd_nav_poi set 
name=replace(name,'＾','^'),
addname=replace(addname,'＾','^'),
othername=replace(othername,'＾','^'),
oldname=replace(oldname,'＾','^'),
address=replace(address,'＾','^');
 

符号圆角半角一样
：“”《》。？ 半角
：“”《》。？　圆角

字段值类型为nvarchar2，在直接使用to_single_byte未生效，加了一个char转换完成了数据调整，部分符号无法转换，需手动replace。