sql常用函数-文本函数

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

转载于：https://www.cnblogs.com/mqxs/p/7380933.html

题目１：

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;

题目２：

https://www.nowcoder.com/practice/26c8715f32e24d918f15db69518f3ad8?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0
４种方法：

select 
-- 替换法 replace(string, '被替换部分','替换后的结果')
-- device_id, replace(blog_url,'http:/url/','') as user_name

-- 截取法 substr(string, start_point, length*可选参数*)，其中start_point从１开始计数。
-- 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;