sql:使用substring

sql:使用substring"/>

sql:使用substring

一、场景描述

需要按照某种格式替换数据库中的数据；
例如：
数据库中openid字段保存着员工工号，现在要把

2_A123_01001234
2_B456_01001234
2_C78912_01001234
2_12345_01001234

替换为

2_A_01001234
2_B_01001234
2_C_01001234
2_1_01001234

也就是2个下划线之间的字段只保留第1位，其余的删除。

二、解决方法

1.听说mysql8后，有一个函数是regexp_replace()，这样的话就内置按照正则替换字符的功能了；但是本人的项目用的是mysql5.7，无法使用这个方法。

2.百度发现，还可以自定义函数实现regexp_replace()，此处略。

3.使用substring_index实现上方的需求，下面会详述。

三、sql样例与说明

update employee t1, (select id, substring(substring_index( substring_index(openid,'_',2),'_',-1),1,1) as zhongjianfrom employee#where substring_index(openid,'_',1) != 1 and openid regexp '^.*_.*_.*') t2set t1.openid = concat (substring_index(openid,'_',1),'_',t2.zhongjian,'_',substring_index(openid,'_',-1)
)  where t1.id=t2.id;

sql说明:
1.首先截取出2个下划线中间的字符串的第1个字符，起别名为zhongjian；

2.然后使用concat拼接出替换后的openid，使用update更新openid字段。

3.#注释了的条件是，不修改openid为包含2个下划线的并且第一个下划线前的字符是1的数据。

函数说明：
1.substring_index(openid,'_',1)的意思是按照下划线分割openid，选取第一个下划线之前的内容；
substring_index(openid,'_',-1)的意思是按照下划线分割openid，选取最后一个下划线之后的内容。

2.substring(openid,1,1)的意思是，选择openid字段，从第1位开始，选取1位截取。

3.openid regexp '^.*_.*_.*'的意思是，按照正则条件选取数据，选取openid的格式符合正则表达式的数据。