文章目录
字符串截取之substring_index、substring(substr)
转载于:https://www.cnblogs.com/mqxs/p/7380933.html
题目1:
https://www.nowcoder.com/practice/f04189f92f8d4f6fa0f383d413af7cb8?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0
select
substring_index(profile, ",", -1) as gender,
count(device_id) as number
from user_submit
group by gender;
或
select
if(profile like "%female", "female", "male") as gender,
count(device_id) as number
from user_submit
group by gender;
题目2:
https://www.nowcoder.com/practice/26c8715f32e24d918f15db69518f3ad8?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0
4种方法:
select
-- 替换法 replace(string, '被替换部分','替换后的结果')
-- device_id, replace(blog_url,'http:/url/','') as user_name
-- 截取法 substr(string, start_point, length*可选参数*),其中start_point从1开始计数。
-- device_id, substr(blog_url,11,length(blog_url)-10) as user_nam
-- 删除法 trim('被删除字段' from 列名)
-- device_id, trim('http:/url/' from blog_url) as user_name
-- 字段切割法 substring_index(string, '切割标志', 位置数(负号:从后面开始))
device_id, substring_index(blog_url,'/',-1) as user_name
from user_submit;
select
device_id,
# replace(blog_url,'http:/url/','') as user_name
# substr(blog_url, 11, length(blog_url)-10) as user_name # substr=substring
# trim("http:/url/" from blog_url) as user_name
substring_index(blog_url, "/", -1) as user_name
from
user_submit;
题目3:
https://www.nowcoder.com/practice/b8d8a87fe1fc415c96f355dc62bdd12f?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0
法1:
select
substring_index(substring_index(profile, ",", -2), ",", 1) as age,
# 例如,先截取后2个字段:“27,male”,再截取其第一个字段,即为所得的age.
count(*) as number
from user_submit
group by age;
法2:
select
substring(profile, 12 ,2) as age, # 从第12个元素开始,截取2个元素。
# substr(profile, 12 ,2) as age,
count(*) as number
from user_submit
group by age;