sql常用函数-文本函数
2022/1/13 2:04:28
本文主要是介绍sql常用函数-文本函数,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
文章目录
- 字符串截取之substring_index、substring(substr)
- 题目1:
- 题目2:
- 题目3:
字符串截取之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;
这篇关于sql常用函数-文本函数的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-01后台管理开发学习:新手入门指南
- 2024-11-01后台管理系统开发学习:新手入门教程
- 2024-11-01后台开发学习:从入门到实践的简单教程
- 2024-11-01后台综合解决方案学习:从入门到初级实战教程
- 2024-11-01接口模块封装学习入门教程
- 2024-11-01请求动作封装学习:新手入门教程
- 2024-11-01登录鉴权入门:新手必读指南
- 2024-11-01动态面包屑入门:轻松掌握导航设计技巧
- 2024-11-01动态权限入门:新手必读指南
- 2024-11-01动态主题处理入门:新手必读指南