mysql 半形轉全形

mysql

ckp6250 2019-08-26 20:37:54 ‧ 4347 瀏覽

　　工作上的需要，要將半形轉成全形，網路上找了一下，好像程式碼都很長，於是，自己胡亂寫一個，請大家看看有無改善空間。

CREATE DEFINER=`root`@`%` FUNCTION `chstr`(`nstr` varchar(100)) RETURNS varchar(200) CHARSET utf8mb4
    NO SQL
BEGIN
	declare nlen,i,nascii int default 1;
	declare vtext,chnumber,nsubstr varchar(200) default '';
	set nlen=char_length(nstr);	
	set chnumber = '　！”＃＄％＆’（）＊＋，－．／０１２３４５６７８９：；＜＝＞？＠ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ〔＼〕︿ˍ‘ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ｛｜｝～';	
	while (i<=nlen) do
		set nsubstr=substr(nstr,i,1);		
		set nascii=ascii(nsubstr);
		set vtext=concat(vtext,if((nascii between 32 and 126),substr(chnumber,(nascii-32)+1,1),nsubstr));
		set i=i+1;
	end while;
	return vtext;
END

使用方法

select chstr('您要測試的中英文abc ABC 123 {})(*$#@');

我希望程式碼越短越好。

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

純真的人

iT邦大師 1 級 ‧ 2019-08-26 22:23:18

嗯...我慣用MSSQL的語法...
如果你找一下相似的MySQL語法~應該可以跟我一樣~

declare @Str nvarchar(max) = '您要測試的中英文abc ABC 123 {})(*$#@'

select replace((
	select (case when UNICODE(substring(@Str,Sort,1)) between 33 and 125 then NCHAR (65248 + UNICODE(substring(@Str,Sort,1))) else substring(@Str,Sort,1) end) + ''
	from (
		select Row_Number()Over(order by number) as Sort
		from master..spt_values
	) as k
	where Sort <= len(@Str)
	for xml path('')
),' ',' ') as StrShow

Mysql 版..我只能解到這程度吧..先忙工作= =||

select Replace(GROUP_CONCAT(Str),',','') as Str1
,Replace(GROUP_CONCAT(Str_Run),',','') as Str2
from (
  select substring(Str,Sort,1) as Str
  ,(case when ORD(substring(Str,Sort,1)) between 33 and 125 then 'x' else substring(Str,Sort,1) end) as Str_Run
  from (
    SELECT @i := @i + 1 as Sort 
    ,Str
    FROM information_schema.columns as a
    ,(
      select @i := 0
      ,'您要測試的中英文abc ABC 123 {})(*$#@' as Str
     ) temp
  ) as k
  where Sort <= char_length(Str)
) as k

http://sqlfiddle.com/#!9/d47893/37

呵...根據你的方式再調整~這樣的確可以轉~

select
GROUP_CONCAT(if((ascii between 32 and 126),substr(chnumber,(ascii-32)+1,1),name) separator '') ReStr
from(
      select
      SUBSTR(cStr, c+1,1) as name,
      ascii(SUBSTR(cStr, c+1,1)) AS ascii
      ,chnumber
      from(
        SELECT (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue) c
        FROM (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
        CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
        CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
        CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
        CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16
        CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 32 SeqValue) TWO_32
      ) as b
      ,(
        SELECT '　！”＃＄％＆’（）＊＋，－．／０１２３４５６７８９：；＜＝＞？＠ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ〔＼〕︿ˍ‘ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ｛｜｝～' as chnumber
        ,'您要測試的中英文abc ABC 123 {})(*$#@' as cStr
      ) as k
      WHERE c <= CHAR_LENGTH(cStr)-1
 ) as a;

http://sqlfiddle.com/#!9/d47893/60

回應 6
分享
檢舉

看更多先前的回應...收起先前的回應...

ckp6250 iT邦好手 1 級 ‧ 2019-08-27 07:07:45 檢舉

感恩！
您的式子，看起來很專業，我的就如同土法練鋼，難登大雅之堂。

不知有沒有那位高僧大德，能將這段語法轉寫成mysql語法。

純真的人 iT邦大師 1 級 ‧ 2019-08-27 13:57:24 檢舉

ckp6250
我轉的MySql的SQL還缺一個函數轉回文字計算~交給你找啦...

ckp6250 iT邦好手 1 級 ‧ 2019-08-27 17:37:50 檢舉

感謝您的提點，如此程式碼看起來優雅多了。
由您處得來的靈感，我改寫成如下程式～

CREATE DEFINER=`marco`@`%` FUNCTION `chstr`(`cStr` text) RETURNS text CHARSET utf8mb4
    NO SQL
BEGIN
	declare cRetStr text;
	declare chnumber varchar(200) default '　！”＃＄％＆’（）＊＋，－．／０１２３４５６７８９：；＜＝＞？＠ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ〔＼〕︿ˍ‘ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ｛｜｝～';
	select
		GROUP_CONCAT(if((ascii between 32 and 126),substr(chnumber,(ascii-32)+1,1),name) separator '') into cRetStr
	from(
		select
			SUBSTR(cStr, c+1,1) as name,
			ascii(SUBSTR(cStr, c+1,1)) AS ascii
		from(
			SELECT (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue) c
			FROM (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
			CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
			CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
			CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
			CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16
			CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 32 SeqValue) TWO_32
		) as b
		WHERE c <= CHAR_LENGTH(cStr)-1
	) as a;
	return cRetStr;
END

另一點報告，經我反覆測試的結果，您的方法執行速度約比我的方法耗時多十倍，但我沒有找出原因。

純真的人 iT邦大師 1 級 ‧ 2019-08-27 17:45:14 檢舉

應該是出自information_schema.columns的讀取問題~

ckp6250 iT邦好手 1 級 ‧ 2019-08-27 19:54:39 檢舉

我也是這麼懷疑，畢竟information_schema要去讀取硬碟。
但，我也有試著加上 limit 20 語法，限制讀取筆數，
但沒有改進多少。

我的式子約耗時0.05秒，您的約需0.5秒，
差了很多，一定有某個環節卡在那裡。

純真的人 iT邦大師 1 級 ‧ 2019-09-01 11:23:58 檢舉

ckp6250
直接套你的方式~這樣的確轉成~
MySql的全型可能要自己定義吧~
已更新在回答~

登入發表回應

舜~

iT邦高手 1 級 ‧ 2019-08-27 11:22:05

您可以參考這篇
https://www.itread01.com/p/1202158.html

這篇是全形轉半形，調整一下或許就可以了

回應 1
分享
檢舉

ckp6250 iT邦好手 1 級 ‧ 2019-08-27 17:41:04 檢舉

感謝您。
這一篇我有測試過，但有bug，不能成功。
另外，我也是一開始就覺得那種寫法不太優雅，所以才自己弄一個。

登入發表回應

一級屠豬士

iT邦新手 2 級 ‧ 2019-08-27 11:38:39

使用Postgresql

select translate('１２３４５６７８９円です', '０１２３４５６７８９', '0123456789');
+-----------------+
|    translate    |
+-----------------+
| 123456789円です |
+-----------------+

select translate('123456789円です', '0123456789', '０１２３４５６７８９');

+--------------------------+
|        translate         |
+--------------------------+
| １２３４５６７８９円です |
+--------------------------+

MySQL 可以使用replace().

Postgresql 另外使用 Oracle的函數

select to_multi_byte('1234567890');
+----------------------+
|    to_multi_byte     |
+----------------------+
| １２３４５６７８９０ |
+----------------------+
(1 row)


select to_single_byte('１２３４５６７８９');
+----------------+
| to_single_byte |
+----------------+
| 123456789      |
+----------------+
(1 row)