中英文字符的映射（TRANSLATE函数的运用）

又有很长时间没有更新博客了=。=
这次想简单介绍一下最近的数据清洗的工作中的发现的一些小方法，因为在网上搜了一下，并没发现有人总结过。

背景

最近处理的数据是多个不同部门的人工录入的数据，莫名其妙的问题非常多。
其中一个就是中英文符号的问题，我发现大部分的中英文符号存在以下关系(代码是Python代码)

for i in range(65281, 65381):
    print(i, chr(i), '=>', i-65248, chr(i-65248))

65281 ！ => 33 !
65282 ＂ => 34 "
65283 ＃ => 35 #
65284 ＄ => 36 $
65285 ％ => 37 %
…省略…
65368 ｘ => 120 x
65369 ｙ => 121 y
65370 ｚ => 122 z
65371 ｛ => 123 {
65372 ｜ => 124 |
65373 ｝ => 125 }
65374 ～ => 126 ~
65375 ｟ => 127 
65376 ｠ => 128 €
65377 ｡ => 129 
65378 ｢ => 130 ‚
65379 ｣ => 131 ƒ
65380 ､ => 132 „

有几个字符还显示不出来=。=
需要注意的是，最后几行例子并不是对应的。但是句号和顿号我感觉还是列出来比较好。
以前我都是使用的replace来做，现在看来非常的扯淡，代码完全没有任何美感了。

Python中的解决方法

因为涉及到的符号比较多，在我这边举例就就说一下有关函数了。
具体要转变哪些符号，大家根据自己的需求来吧。
ps：我刚学Python的时候，根本没有多去关注translate函数，感觉用法很蠢，如今真香！

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.rand(3,2), dtype=np.str)
df[0]

0    0.8857066754596298
1    0.6980269340245522
2    0.1620513328026375
Name: 0, dtype: object

df[0].str.translate(str.maketrans('123', '!@#', '45')) # 替换123，删除45

ps：值得一提的是，我看了好久其他大佬的案例，才发现代码里的这个str.maketrans中的str就是Python里的基础类str。
我总以为是其他库（例如string库）被重命名为str了。

0      0.887066796@98
1      0.6980@69#0@@@
2    0.!6@0!##@80@6#7
Name: 0, dtype: object

df[0].str.translate(str.maketrans('1235', '!@#%', '1234')) # maketrans函数中的，第三个参数中的字符会覆盖前两个参数

0    0.88%70667%%9698
1         0.6980690%%
2          0.60%8067%
Name: 0, dtype: object

Oracle中的解决方法

CREATE OR REPLACE
FUNCTION STANDARDIZE_STR(TEXT0 IN VARCHAR2)
RETURN VARCHAR2
AS
	res VARCHAR2;
BEGIN
	res := TEXT0;
	res := TRANSLATE(res, 'Dyson!@#$%', 'Dyson'); --见代码后的“注”
	res := TRANSLATE(res, '123', 'abc'); 
	RETURN res;
END;

由于涉及的符号替换可能会比较多，所以我就写在了ORACLE函数中，再到SQL查询里调用。
由于我原来的代码我暂时没法复制出来（安全管理严格），所以我这部分代码没有运行过，敲错的话还请见谅。

注：
此处有点微妙，在Oralce中，是可以用TRANSLATE函数来删除特定的一些字符的。但是用法有点奇怪，不知道是不是我没有get到正确方式。
理论上，当TRANSLATE的第二个参数的长度应该与第三个参数的长度一样的，这样才能一一对应。但是在Oracle中，第二个参数的长度是允许大于第三个参数的长度的，多出来的部分，视为需要删除的部分=。=
所以我这行代码的意思，其实是为了删除“!@#$%”这些符号。

MySQL中的解决方法

#创建前删除已经创建的自定义函数
DROP FUNCTION IF EXISTS translate_str;
#创建函数
DELIMITER $$  
CREATE FUNCTION `translate_str`(str0 LONGTEXT,from_str VARCHAR(1000),to_str VARCHAR(1000))  
	RETURNS LONGTEXT
	DETERMINISTIC  
BEGIN  
	DECLARE i0 INT;
	DECLARE f_str VARCHAR(1000);
	DECLARE t_str VARCHAR(1000);
 
	# 本函数是在仿制oracle的translate函数
	# 具体函数用法参考https://blog.csdn.net/weixin_39461443/article/details/102684234
	# 若from_str没有to_str长，那么to_str超长的部分将会被忽略
	# 若from_str比to_str长，那么from_str超长的部分将视为需要被删除的字符

	SET i0 = 1;

	# 循环查找以替换所有的目标字符串
	WHILE i0 <= CHAR_LENGTH(from_str) DO
		SET f_str = SUBSTR(from_str, i0, 1);
		SET t_str = SUBSTR(to_str, i0, 1);
		
		# 若存在from_str更长的情况，超长部分将被替换为空字符串
		IF t_str IS NULL THEN
			SET t_str = '';
		END IF;
		
		# 替换对应的字符串
		IF INSTR(str0, f_str) > 0 THEN
			SET str0 = REPLACE(str0, f_str, t_str);
		END IF;
-- 		
		SET i0 = i0 + 1;
	END WHILE;  

	RETURN str0;
END$$  
DELIMITER ;