oracle全角字符转半角 Oracle 表字段全角字符轉換半角字符辦法

在Oralce的某一個表中，一個地址字段包含了全角的字符，需要轉換為半角字符，以便進一步處理。

表名：a

包含全角字符的字段：address

編輯表結構，添加一列：address2

在PLSQL中，新建SQL窗口，輸入：update a set a.address2 =(to_single_byte(a.address))

執行，提交，即可。

函數解釋：

轉換成半角函數：

to_single_byte(s)

轉換成

全角

函數:

to_multi_byte(s)

以上這個to_single_byte(s)和to_multi_byte(s)函數發揮作用的前提是數據庫字符集包括單字節字符和多字節字符。

我的另外一台數據庫安裝的是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')

手工寫入這些好麻煩，有沒有自動化的一點的辦法？

如果觉得《oracle全角字符转半角 Oracle 表字段全角字符轉換半角字符辦法》对你有帮助，请点赞、收藏，并留下你的观点哦！