给定如下salary数据,我们希望能够将salary的上下限截取,新增两列上下限数据,例如8k-15k我们希望拆分为8和15这两列。
1.薪资下限截取
- 首先,用
locate
获取第一个k的位置
locate(str1,str2,pos)
返回子串str1从位置pos处开始,第一次出现在字符串str2中的位置,pos不写默认从头找
代码
SELECT salary,locate('k',salary)
FROM data.dataanalyst_sql
;
- 接着,用
left
函数截取k位置之前的数据
left(str,n)
截取str左边n个字符
SELECT salary ,locate('k',salary),
left(salary,locate('k',salary)-1) as "薪资下限"
FROM data.dataanalyst_sql
;
- 最后,得到薪资上限如下
2.薪资下限截取
- 首先,用
locate
获取第二个k
的位置和-
的位置
从salary第四个位置开始查找k,这样就把第一个k排除在外
SELECT salary,
locate('k',salary,4),
locate('-',salary)
FROM data.dataanalyst_sql
;
- 接着,用
substring
函数截取k位置之前的数据
substring(str,index,len)
从str的index位置截取len个字符
截取位置:
-
的位置+1
截取长度:第二个k
下标--
下标-1
SELECT salary,
locate('k',salary,4),
locate('-',salary),
substring(salary,locate('-',salary)+1,locate('k',salary,4)-locate('-',salary)-1)
FROM data.dataanalyst_sql
;
结果如下
可以看到,会有如15k以上的“脏数据”,换种思路,我们将截取长度换成:
截取位置:
-
的位置+1
截取长度:总长度--
下标-1
SELECT salary,
locate('k',salary,4),
locate('-',salary),
substring(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1)
FROM data.dataanalyst_sql
;
结果如下