示例: SELECT ff,ID,姓名,GROUP_CONCAT(NEWXM ORDER BY NEWID SEPARATOR'') FROM ( SELECT A.*,B.id AS NEWID, IF(MID(姓名,B.id,1) REGEXP '[^ -~]' =1, REPLACE(MID(姓名,B.id,1),MID(姓名,B.id,1),''),MID(姓名,B.id,1)) AS NEWXM FROM TRY A LEFT JOIN LSB1 B ON CHAR_LENGTH(TRIM(A.姓名))>=B.id ORDER BY A.ID,NEWID ) C GROUP BY ff,ID,姓名 ;LSB1只有1个ID字段,1-100000,取决于字符长度 在MYSQL5下测试通过
SELECT ff,ID,姓名,GROUP_CONCAT(NEWXM ORDER BY NEWID SEPARATOR'') FROM (
SELECT A.*,B.id AS NEWID,
IF(MID(姓名,B.id,1) REGEXP '[^ -~]' =1,
REPLACE(MID(姓名,B.id,1),MID(姓名,B.id,1),''),MID(姓名,B.id,1)) AS NEWXM
FROM TRY A LEFT JOIN LSB1 B ON CHAR_LENGTH(TRIM(A.姓名))>=B.id ORDER BY A.ID,NEWID
) C GROUP BY ff,ID,姓名 ;LSB1只有1个ID字段,1-100000,取决于字符长度
在MYSQL5下测试通过
用其他正则替换工具搞好了,直接导入就可以了!