Oracle 表字段全角字符转换半角字符办法

最新推荐文章于 2024-06-04 23:58:59 发布

最新推荐文章于 2024-06-04 23:58:59 发布 · 3k 阅读

文章标签：

#ORACLE #全角字符 #半角字符

ORACLE 同时被 2 个专栏收录

46 篇文章

订阅专栏

字符集

5 篇文章

订阅专栏

本文介绍如何在Oracle数据库中将表内的全角字符转换为半角字符的方法。提供了使用to_single_byte函数直接转换和利用replace函数逐一手动替换两种方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

如何把oracle表中的全角字符转换为半角字符？

a表包含全角字符的字段：address
update a set a.address =(to_single_byte(a.address))

执行，提交，即可。函数解释：

转换成半角函数：  to_single_byte(s)

转换成全角函数:   to_multi_byte(s)

但是，to_single_byte(s)和to_multi_byte(s)函数发挥作用的前提是数据库字符集包括单字节字符和多字节字符。

oracle数据库安装的是GBK字符集，但是查看表的内码，发现表的数据是用unicode字符集存储的，暂时还未搞明白是怎么回事。。。。。
如果数据库不包含这些字符，需要用repalce()函数，分别手工去替换：

update a t set t.address = replace(t.address,'Ａ','A')
update a t set t.address = replace(t.address,'Ｂ','B')
update a t set t.address = replace(t.address,'Ｃ','C')
update a t set t.address = replace(t.address,'Ｄ','D')
update a t set t.address = replace(t.address,'Ｅ','E')
update a t set t.address = replace(t.address,'Ｆ','F')
update a t set t.address = replace(t.address,'Ｇ','G')
update a t set t.address = replace(t.address,'Ｈ','H')
update a t set t.address = replace(t.address,'Ｉ','I')
update a t set t.address = replace(t.address,'Ｊ','J')
update a t set t.address = replace(t.address,'Ｋ','K')
update a t set t.address = replace(t.address,'Ｌ','L')
update a t set t.address = replace(t.address,'Ｍ','M')
update a t set t.address = replace(t.address,'Ｎ','N')
update a t set t.address = replace(t.address,'Ｏ','O')
update a t set t.address = replace(t.address,'Ｐ','P')
update a t set t.address = replace(t.address,'Ｑ','Q')
update a t set t.address = replace(t.address,'Ｒ','R')
update a t set t.address = replace(t.address,'Ｓ','S')
update a t set t.address = replace(t.address,'Ｔ','T')
update a t set t.address = replace(t.address,'Ｕ','U')
update a t set t.address = replace(t.address,'Ｖ','V')
update a t set t.address = replace(t.address,'Ｗ','W')
update a t set t.address = replace(t.address,'Ｘ','X')
update a t set t.address = replace(t.address,'Ｙ','Y')
update a t set t.address = replace(t.address,'Ｚ','Z')
update a t set t.address = replace(t.address,'－','-')
update a t set t.address = replace(t.address,'０','0')
update a t set t.address = replace(t.address,'１','1')
update a t set t.address = replace(t.address,'２','2')
update a t set t.address = replace(t.address,'３','3')
update a t set t.address = replace(t.address,'４','4')
update a t set t.address = replace(t.address,'５','5')
update a t set t.address = replace(t.address,'６','6')
update a t set t.address = replace(t.address,'７','7')
update a t set t.address = replace(t.address,'８','8')
update a t set t.address = replace(t.address,'９','9')

手工写入这些好麻烦，有没有自动化的一点的办法？