专栏首页SQL实现SQL 从字符串中提取数字

SQL 从字符串中提取数字

mix 表有一个 varchar 类型的字段 v,该字段的允许长度只有 15 位,但它存储的数据比较混杂。

mix 表的数据:

    id  v       
------  --------
     1  123     
     2  abc     
     3  1d3     
     4  0       
     5  123.0   
     6  0123    
     7  01#123  
     8  0$123   

我们希望能从字段 v 的数据中提取出所有数字,重新组合成数值(数字在字符串中出现的相对顺序不变)。

期待得到的结果:

    id  v       mix     
------  ------  --------
     1  123     123     
     3  1d3     13      
     4  0       0       
     5  123.0   1230    
     6  0123    0123    
     7  01#123  01123   
     8  0$123   0123    

一种可行的方法是:把原字符串拆分成一个个字符,然后过滤掉非数字字符,最后把剩下的数字按照出现的顺序组合成数值。

把字符串拆分成多个字符,可以使用递归的方式实现,也可以先和数字辅助表(有 1 ~ 15的自然数)做笛卡尔积连接,再分割出每个字符。

先来看比较简单的实现方案,也就是使用笛卡尔积的实现方案。

SELECT 
    mix.id AS id,
    v,
    SUBSTR(v, t20.id, 1) AS s,
    t20.id AS seq 
  FROM
    mix,
    t20 
  WHERE t20.id <= CHAR_LENGTH(v) 
  ORDER BY mix.id,
    t20.id
    

    id  v       s          seq  
------  ------  ------  --------
     1  123     1              1
     1  123     2              2
     1  123     3              3
     2  abc     a              1
     2  abc     b              2
     2  abc     c              3
     3  1d3     1              1
     3  1d3     d              2
     3  1d3     3              3
     4  0       0              1
     ...

其中,t20 是由 1 ~ 20 组成的数字辅助表。考虑到有可能出现中文汉字,在 SQL 中使用了 CHAR_LENGTH() 函数,而不是LENGTH()

从打印的结果中可以看出,我们已经将字符串拆分成单个字符,并且还保持了字符出现的相对顺序。

最后,我们将非数字的字符过滤掉,再使用GROUP_CONCAT() 将数字字符拼接到一块。

SELECT 
  id,
  v,
  GROUP_CONCAT(s 
    ORDER BY seq SEPARATOR '') AS mix 
FROM
  (SELECT 
    mix.id AS id,
    v,
    SUBSTR(v, t20.id, 1) AS s,
    t20.id AS seq 
  FROM
    mix,
    t20 
  WHERE t20.id <= CHAR_LENGTH(v) 
  ORDER BY v,
    t20.id) t 
WHERE s >= '0' 
  AND s <= '9' 
GROUP BY v,
  id 
ORDER BY id 

再来看递归的方式,它的实现有点复杂,我把完整的 SQL 放这了。

WITH RECURSIVE chaos (id, v, s, seq) AS 
(SELECT 
  id,
  v,
  SUBSTR(v, 1, 1) AS s,
  1 AS seq 
FROM
  mix 
UNION ALL 
SELECT 
  id,
  v,
  SUBSTR(v, seq + 1, 1),
  seq + 1 
FROM
  chaos 
WHERE seq <= CHAR_LENGTH(v) - 1) 

SELECT 
  id,
  v,
  GROUP_CONCAT(s 
    ORDER BY seq SEPARATOR '') AS mix 
FROM
  chaos 
WHERE s >= '0' 
  AND s <= '9' 
GROUP BY v,
  id 
ORDER BY id 

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-11-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • python提取字符串中数字

    题目:【这是一个复杂问题的简化】如下是一个字符串列表,提取字符串中第二个数字,并判断是否大于1000,如果是,从列表中删除这一行。

    用户7886150
  • 【正则表达式】从字符串中提取数字

    蛮三刀酱
  • Excel公式技巧11: 从字符串中提取数字——数字位于字符串末尾

    上篇文章讲解了提取位于字符串开头的数字的公式技术,本文研究从字符串开头提取数字的技术:

    fanjy
  • Excel公式技巧10: 从字符串中提取数字——数字位于字符串开头

    2. 字符串中除开头外其他地方也有数字,要么在末尾,要么在中间,例如123ABC456或123ABC456DEF。

    fanjy
  • 字符串提取函数

    今天要跟大家分享三个excel中使用频率最高的字符串提取函数——left/right/mid函数。 ▽▼▽ 这三个函数分别对用截取某一单元格文本的左、右、中间某...

    数据小磨坊
  • 字符串提取

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    喜欢ctrl的cxk
  • iOS提取字符串

    czjwarrior
  • 127-提取字符串

    第二个需求是去除字符串开头的非字母字符,这个功能的实现只要找到左边第一字母的下标,然后取切片。 以下通过两个方法完成第二点要求:

    凯茜的老爸
  • python 正则之提取字符串中的汉字,数字,字母

    学到老
  • Excel公式技巧13: 从字符串中提取数字——将所有数字提取到单个单元格

    前三篇文章分别讲解了提取位于字符串开头和末尾的数字的公式技术、提取字符串中所有的数字并放在不同的单元格中的公式技术,本文研究从字符串中提取所有数字并将这些数字作...

    fanjy
  • 提取数字——字符串、正则面试题

    提取数字——字符串、正则面试题 HTML5学堂:正则、数组、字符串,是JavaScript语言中让人头痛的一些知识,今天这篇文章我们使用数组字符串、正则两种方法...

    HTML5学堂
  • js提取正则中的字符串

    代码如下: var results = data.match(/(start=').*?(')/); if (results != null) { co...

    Java中文社群-磊哥
  • SQL SERVER拼接字符串(字符串中有变量)

      3、若在执行时存在类型转换错误,则应用相应的类型转换函数,对变量进行类型转换(如cast()函数)。

    挽风
  • Excel公式技巧22: 从字符串中提取指定长度的连续数字子串

    本文给出了一种从可能包含若干个不同长度的数字的字符串中提取指定长度的数字的解决方案。在实际的工作表中,存在着许多此类需求,例如从字符串中获取6位数字账号。

    fanjy
  • Excel公式技巧12: 从字符串中提取数字——将所有数字分别提取到不同的单元格

    前两篇文章分别讲解了提取位于字符串开头和末尾的数字的公式技术,本文研究从字符串中提取所有数字的技术:

    fanjy
  • Excel字符串中提取数字,不需要大神级公式

    大海:如果是以前,我会用VBA,现在,我用Power Query,而且很简单,大家都能学会的。

    大海Power
  • golang 中获取字符串个数

    在 golang 中不能直接用 len 函数来统计字符串长度,查看了下源码发现字符串是以 UTF-8 为格式存储的,说明 len 函数是取得包含 byte 的个...

    Rolle
  • shell正则提取字符串中的数字并保存到变量中

    tr是translate的缩写,主要用于删除文件中的控制字符,或者进行字符转换

    休辞醉倒
  • MySQL 中对字符串进行操作:字符串截取

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    学到老

扫码关注云+社区

领取腾讯云代金券