sql常用函数-文本函数

文章目录

字符串截取之substring_index、substring(substr)

转载于:https://www.cnblogs.com/mqxs/p/7380933.html
sql常用函数-文本函数

题目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;
上一篇:Linux内核各模块启动时间分析【转】


下一篇:vant3图片二进制上传