【峰回路转】Excel技巧百例 14.字符串截取函数的经典组合示例

原始数据:


我们可以看到这些url的长度不同,后缀不同,层级也不同,我们如何快速得到如下结果:


逻辑说明:就是得到最后一个“/”和"."之间的数字,如果中间的层级相同,我们可以采用用符号分列的方式,如果后缀相同或是数字都是相同长度,我们可以Ctrl+F替换,然后截取固定长度,如果数据很多,不能保证中间的数字是相同长度的,我们应该如何处理呢?

下面使用函数对字符串进行一下处理:

菜单功能一览表 【公农双历查询】【高级定位】【选区背景着色】【修改文件建立时间】【工作表环境设置】【按颜色合计】【根据工资计算钞票】【隔行插入行】【折分工作簿(工作表)】【工作表折分】【合并工作簿】【文本与数值互换】【复选框工具】【报表分栏工具箱】【删除工资条恢复明细表】【制作工资条】【建立分页小计】【删除分页小计】【建立图片目录】【批量导入图片(精确匹配)】【批量导入图片(模糊匹配)】【批量导入图片到批注】【删除所有图片】【批量导出图片】【图片查询工具】【将选区保存为图片】【生成个性化批注】 【插入GIF动画】【插入Flash动画】【禁止录入重复值】【相同项与不同项】【建文件目录】【提取选区重复值】【清除列中重复值】【标示重复值】【删除空白单元格所在行】【筛选唯一值】【建工作表目录】【批量新建复制工作表】【批量加解密】【工作表批量命名】【破解工作表/簿密码】 【批量命名文件】【可还原的合并】【合并列中相同值】【取消合并还原数据】【合并区域自动换行】【合并到选区】【合并数据并复制】【反向选择】【文本、数字分离与计算】【保护公式】【生成千年日历】【百家姓与字母序列】【一键隐藏非使用区】生成斜线表头】【打印当前页】【双面打印】【生成底端标题】【简体转繁体】【繁体转简体】【生成系统图标】【获取内置命令】【修复Excel】【破解VBA密码】【删除空单元格】【转置选区】【按列倒置】【按列倒置】【字母大小写转换】【小写金额转大写】【大写金额转小写】【区域数据加密】【多区域复制】【按颜色筛选】【按颜色排序】【返回首页】 自定义函数一览表 【sumifcol】【AVER】【hesum】【NOWW】【SFZ】【批注】【合并】【取数】【唯一值】【消除空值】【颜色求和】【颜色计数】【工作表】【数字】【分割取数】【共有项】不同项】【【公式】【计算】【公式长度】【大写】【排名】【排序】【替换】【重复】【文件目录】【File】 【公农双历查询】:生成多功能日历,可以查询所有节、假日和农历 【高级定位】:多功能选择(查找)工具。可以选择大于某值或者小于某值或者在某范围之间的值,文本定位时支持通配符。还可以按格式查找/定位 【背景着色】:将当前或者列进行颜色标示,以突出显示,有利于数据查看。可以随心所欲地定义颜色,还可以自由调整颜色的深浅。本工具相对于同类工具有不破坏背景色、条件格式、复制粘贴和撤消功能之优点 【环境设置】:设置工作表界面视图,控制各项目的显示与隐藏 【修改文件时间】:随心所欲修改文件的创建时间 【按颜色汇总】:按背景色对选区的数据合类合计 【反向选择】:选择当前区域中未选择的区域 【千年日历】:工单元格中生成千年日期。默认显示本月的日历,可以自由调整年月 【保护公式】:保护当前工作表所有公式,不让人看到公式本身,只能看到公式结果 【生成斜线表头】:Excel没有Word那样的斜线表头工具,本工具可以弥补此不足。包括单线、双线可选 【百家姓与字母序列】:运行后可以在单元格中进行百家姓与字母填充,提升录入速度 【一键隐藏非使用区】:对空白区域瞬间隐藏起来。可以选择作用对象是当前表还是所有工作表。恢复时也只要瞬间完成 【字符分离及计算】:批量地对单元格进行文本、数字分离,还可以计算取出的表达式 【删除空单元格】:删除选区的空单元格,后面的数据自动上升 【转置选区】:将选区行列调换 【按列倒置】:将选区的数据横向倒置 【按列倒置】:将选区的数据纵向倒置 【字母大小写转换】:将选区的单词、字母在大写小写、首字母大写之间转换 【小写金额转大写】:将小写金额批量转换成大写 【大写金额转小写】:将大写金额批量转换成小写 【区域数据加密】:对工作表选区的数据进行加密,转换成乱码,有密码才可以查看。 【简体转繁体】:将简体字批量转换成繁体 【繁体转简体】:将繁体字批量转换成简体 【根据工资计算钞票】:根据员工的工资计算需要多少张100元、50元......1元的钞票,可以批量计算。发现金工资的财务工作者必备 【隔行插入行】:对工作表隔行插入行,或者隔列插入列,其中行数可以自定义 【折分工作簿】:将指定工作簿的每个工作表拆分成单独的工作簿,新工作簿名称等于原工作表名称 【工作表折分】:将当前工作表的数据按条件拆分成多个工作表,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作表合到当前工作簿是,表与表对应;将不同工作簿中同工作表的数据合并到同一工作表中。差异在于同名工作表的处理 【文本与数值互换】:将选区的数字瞬间转换成文本;将选区的文本型数字瞬间转换成数值 【
在 MySQL 中实现多个字符拆分并并行转列操作,可借助序号表和字符串处理函数。 ### 原理 利用 `SUBSTRING_INDEX` 函数按指定分隔符拆分字符串,结合序号表生成连续序号,通过交叉连接(`CROSS JOIN`)将序号与拆分后的字符串元素关联,实现行转列。 ### 示例代码 #### 场景一:拆分 `user` 表中 `mobile` 字段 假设 `user` 表存储用户信息,`mobile` 字段以逗号分隔存储多个手机号码。使用以下 SQL 语句拆分并转列: ```sql SELECT NAME, REPLACE( SUBSTRING_INDEX(mobile, ',', a.id), CONCAT( SUBSTRING_INDEX(mobile, ',', a.id - 1), ',' ), '' ) AS mobile FROM squence a CROSS JOIN ( SELECT NAME, CONCAT(mobile, ',') AS mobile, LENGTH(mobile) - LENGTH(REPLACE(mobile, ',', '')) + 1 AS size FROM `user` ) b ON a.id <= b.size; ``` 此 SQL 借助 `squence` 表生成连续 `id`,通过交叉连接将 `id` 与 `user` 表中 `mobile` 字段拆分后的元素关联,最终实现 `mobile` 字段拆分并转列 [^2]。 #### 场景二:拆分 `rfsr` 表中 `FAIL_REASON` 字段 假设 `rfsr` 表存储金融服务结果信息,`FAIL_REASON` 字段以逗号或竖线分隔存储多个失败原因。使用以下 SQL 语句拆分并转列: ```sql SELECT ids, id, APPLY_ID, REPLACE( SUBSTRING_INDEX(REASON, ',', a.ids), CONCAT( SUBSTRING_INDEX(REASON, ',', a.ids - 1), ',' ), '' ) AS REASON FROM t_squence a CROSS JOIN ( SELECT id, APPLY_ID, CONCAT(REASON, ',') AS REASON, LENGTH(REASON) - LENGTH(REPLACE(REASON, ',', '')) + 1 AS size FROM ( SELECT id, APPLY_ID, left(substring(REPLACE(REASON,',,',','),2),length(substring(REPLACE(REASON,',,',','),2))-1) REASON FROM ( SELECT id, APPLY_ID, CERT_NO, REPLACE(GROUP_CONCAT(FAIL_REASON),'|',',') AS REASON FROM rcs_finance_ser_result rfsr WHERE rfsr.final_result='AD' AND rfsr.cert_no='411201199110164491' GROUP BY rfsr.CERT_NO ) t ) tt ) b ON a.ids <= b.size; ``` 此 SQL 先对 `FAIL_REASON` 字段进行预处理,将竖线替换为逗号,去除多余逗号,再借助 `t_squence` 表生成连续 `ids`,通过交叉连接将 `ids` 与 `FAIL_REASON` 字段拆分后的元素关联,最终实现 `FAIL_REASON` 字段拆分并转列 [^3]。 ### 代码解释 - `SUBSTRING_INDEX(str,delim,count)`:按指定分隔符 `delim` 拆分字符串 `str`,`count` 为正返回定界符左边内容,为负返回右边内容 [^1]。 - `REPLACE(str,from_str,to_str)`:将字符串 `str` 中 `from_str` 替换为 `to_str`。 - `LENGTH(str)`:返回字符串 `str` 的长度。 - `GROUP_CONCAT(expr)`:将分组后的数据连接成一个字符串。 - `CROSS JOIN`:实现两个表的交叉连接,生成笛卡尔积。 ### 注意事项 - 需创建序号表(如 `squence` 或 `t_squence`),其 `id` 或 `ids` 范围应根据实际拆分后元素数量确定。 - 若字符串包含多种分隔符,需先统一分隔符,再进行拆分操作。
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值