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的格式符合正则表达式的数据。
更多推荐
sql:使用substring
发布评论