SQL 提取第n个分隔子字符串|极客教程

SQL 提取第n个分隔子字符串，你想从一个字符串里提取出特定的分隔子字符串。考虑下面的视图 V，它生成了本问题的源数据。

SQL 提取第n个分隔子字符串问题描述

你想从一个字符串里提取出特定的分隔子字符串。考虑下面的视图 V，它生成了本问题的源数据。

create view V as
select 'mo,larry,curly' as name
  from t1
 union all
select 'tina,gina,jaunita,regina,leena' as name
  from t1

上述视图输出如下所示的数据。

select * from v
　
NAME
--------------------
mo,larry,curly
tina,gina,jaunita,regina,leena

你希望提取每一行的第二个名字，并得到下面这样的结果集。

  SUB
-----
larry
 gina

SQL 提取第n个分隔子字符串解决方案

解决这一问题的关键是，把每一个名字转换为单独的一行，并保持每一个名字在列表里的顺序不变。具体方法取决于你所使用的数据库。
DB2
遍历视图 V 返回的 NAME，并使用 ROW_NUMBER 函数筛选出每一个字符串里的第二个名字。

 1 select substr(c,2,locate(',',c,2)-2)
 2   from (
 3 select pos, name, substr(name, pos) c,
 4        row_number() over(partition by name
 5                        order by length(substr(name,pos)) desc) rn
 6   from (
 7 select ',' ||csv.name|| ',' as name,
 8        cast(iter.pos as integer) as pos
 9   from V csv,
10        (select row_number() over() pos from t100 ) iter
11  where iter.pos <= length(csv.name)+2
12        )  x
13  where length(substr(name,pos)) > 1
14    and substr(substr(name,pos),1,1) = ','
15        ) y
16  where rn = 2

MySQL
遍历视图 V 返回的 NAME，并使用逗号的位置来筛选出每一个字符串里的第二个名字。

 1 select name
 2   from (
 3 select iter.pos,
 4        substring_index(
 5        substring_index(src.name,',',iter.pos),',',-1) name
 6   from V src,
 7        (select id pos from t10) iter,
 8  where iter.pos <=
 9        length(src.name)-length(replace(src.name,',',''))
10        ) x
11 where pos = 2

Oracle
遍历视图 V 返回的 NAME，并使用 SUBSTR 函数和 INSTR 函数提取每个列表里的第二个名字。

 1 select sub
 2   from (
 3 select iter.pos,
 4        src.name,
 5        substr( src.name,
 6         instr( src.name,',',1,iter.pos )+1,
 7         instr( src.name,',',1,iter.pos+1 ) -
 8         instr( src.name,',',1,iter.pos )-1) sub
 9   from (select ','||name||',' as name from V) src,
10        (select rownum pos from emp) iter
11  where iter.pos < length(src.name)-length(replace(src.name,','))
12        )
13  where pos = 2

PostgreSQL
使用 SPLIT_PART 函数把每一个单独的名字作为一行返回。

 1 select name
 2   from (
 3 select iter.pos, split_part(src.name,',',iter.pos) as name
 4   from (select id as pos from t10) iter,
 5        (select cast(name as text) as name from v) src
 7  where iter.pos <=
 8         length(src.name)-length(replace(src.name,',',''))+1
 9        ) x
10  where pos = 2

SQL Server
遍历视图 V 返回的 NAME，并使用 ROW_NUMBER 函数筛选出每一个字符串里的第二个名字。

 1 select substring(c,2,charindex(',',c,2)-2)
 2   from (
 3 select pos, name, substring(name, pos, len(name)) as c,
 4        row_number() over(
 5         partition by name
 6         order by len(substring(name,pos,len(name))) desc) rn
 7   from (
 8 select ',' + csv.name + ',' as name,
 9         iter.pos
10   from V csv,
11        (select id as pos from t100 ) iter
12  where iter.pos <= len(csv.name)+2
13        ) x
14  where len(substring(name,pos,len(name))) > 1
15    and substring(substring(name,pos,len(name)),1,1) = ','
16        ) y
17  where rn = 2

SQL 提取第n个分隔子字符串扩展知识

DB2 和 SQL Server
这两种数据库的解决方案的语法稍有不同，但方法相同。后面的讨论里我们以 DB2 数据库的解决方案为主。使用内嵌视图 X 遍历字符串，结果如下所示。

select ','||csv.name|| ',' as name,
        iter.pos
  from v csv,
        (select row_number() over() pos from t100 ) iter
 where iter.pos <= length(csv.name)+2
　
EMPS                                 POS
-------------------------------     ----
,tina,gina,jaunita,regina,leena,       1
,tina,gina,jaunita,regina,leena,       2
,tina,gina,jaunita,regina,leena,       3
...

然后遍历字符串中的每一个字符。

select pos, name, substr(name, pos) c,
        row_number() over(partition by name
                        order by length(substr(name, pos)) desc) rn
  from (
select ','||csv.name||',' as name,
       cast(iter.pos as integer) as pos
  from v csv,
      (select row_number() over() pos from t100 ) iter
 where iter.pos <= length(csv.name)+2
       ) x
 where length(substr(name,pos)) > 1
　
POS  NAME             C                 RN
---  ---------------  ----------------  --
  1  ,mo,larry,curly, ,mo,larry,curly,   1
  2  ,mo,larry,curly, mo,larry,curly,    2
  3  ,mo,larry,curly, o,larry,curly,     3
  4  ,mo,larry,curly, ,larry,curly,      4
  ...

现在，我们得到了含有字符串不同部分的数据，并且很容易筛选出要保留的行。我们感兴趣的行都以逗号开头，其余的行都将被舍弃。

select pos, name, substr(name,pos) c,
       row_number() over(partition by name
                       order by length(substr(name, pos)) desc) rn
  from (
select ','||csv.name||',' as name,
       cast(iter.pos as integer) as pos
  from v csv,
        (select row_number() over() pos from t100 ) iter
 where iter.pos <= length(csv.name)+2
        ) x
 where length(substr(name,pos)) > 1
    and substr(substr(name,pos),1,1) = ','
　
POS   NAME                              C                                RN
 ---   --------------                   ----------------                 --
  1   ,mo,larry,curly,                  ,mo,larry,curly,                  1
  4   ,mo,larry,curly,                  ,larry,curly,                     2
 10   ,mo,larry,curly,                  ,curly,                           3
  1   ,tina,gina,jaunita,regina,leena,  ,tina,gina,jaunita,regina,leena,  1
  6   ,tina,gina,jaunita,regina,leena,  ,gina,jaunita,regina,leena,       2
 11   ,tina,gina,jaunita,regina,leena,  ,jaunita,regina,leena,            3
 19   ,tina,gina,jaunita,regina,leena,  ,regina,leena,                    4
 26   ,tina,gina,jaunita,regina,leena,  ,leena,                           5

这是确定如何得到第 n 个子字符串的重要一步。注意，由于如下所示的 WHERE 条件，许多行已经被删除。

substr(substr(name,pos),1,1) = ','

注意，,larry,curly 这个字符串的原排名为 4，现在的排名却变成了 2。由于 WHERE 子句会在 SELECT 之前执行，因此以逗号开头的行会先被筛选出来，之后才调用 ROW_NUMBER 函数决定每一行的编号。此时可以很清楚地看到，要得到第 n 个子字符串，只需要在 WHERE 子句里指定 RN 等于 n 即可。最后，只保留我们感兴趣的行（本例中是 RN 等于 2 的行），并调用 SUBSTR 函数提取那一行的名字。最后留下来的是每行中的第一个名字：,larry,curly,里的 larry 和,gina,jaunita,regina,leena,里的 gina。
MySQL
使用内嵌视图 X 遍历每个字符串，我们可以通过计算字符串中的分隔符的个数来确定该字符串中有多少个值。

select iter.pos, src.name
  from (select id pos from t10) iter,
       V src
 where iter.pos <=
       length(src.name)-length(replace(src.name,',',''))
　
    +------+--------------------------------+
    | pos  |                           name |
    +------+--------------------------------+
    |    1 | mo,larry,curly                 |
    |    2 | mo,larry,curly                 |
    |    1 | tina,gina,jaunita,regina,leena |
    |    2 | tina,gina,jaunita,regina,leena |
    |    3 | tina,gina,jaunita,regina,leena |
    |    4 | tina,gina,jaunita,regina,leena |
    +------+--------------------------------+

上述查询结果中，每个字符串对应的数据行相较于字符串里实际的值的个数少了一行，因为这就是我们需要的。SUBSTRING_INDEX 函数可以解析我们需要的这些值。

select iter.pos,src.name name1,
        substring_index(src.name,',',iter.pos) name2,
        substring_index(
         substring_index(src.name,',',iter.pos),',',-1) name3
  from (select id pos from t10) iter,
       V src
 where iter.pos <=
       length(src.name)-length(replace(src.name,',',''))
　
+------+--------------------------------+--------------------------+---------+
| pos  | name1                          | name2                    | name3   |
+------+--------------------------------+--------------------------+---------+
|    1 | mo,larry,curly                 | mo                       | mo      |
|    2 | mo,larry,curly                 | mo,larry                 | larry   |
|    1 | tina,gina,jaunita,regina,leena | tina                     | tina    |
|    2 | tina,gina,jaunita,regina,leena | tina,gina                | gina    |
|    3 | tina,gina,jaunita,regina,leena | tina,gina,jaunita        | jaunita |
|    4 | tina,gina,jaunita,regina,leena | tina,gina,jaunita,regina | regina  |
+------+--------------------------------+--------------------------+---------+

我已经展示了 3 个和名字相关的字段，我们可以据此了解嵌套的 SUBSTRING_INDEX 函数是如何工作的。内层的函数能够找到逗号第 n 次出现的位置，并提取该位置左侧的全部字符。外层的函数可以找到（从字符串的末尾开始计数）逗号第一次出现的位置，并提取其右侧的全部字符。最后，将 POS 等于 n 的 NAME3 值保留下来，本例中 n 为 2。
Oracle
使用内嵌视图遍历每一个字符串。字符串在内嵌视图结果集里出现的次数取决于字符串里有多少个值。本解决方案通过计算字符串里分隔符的出现次数得到每个字符串含有多少个值。因为字符串前后都有逗号，字符串里值的个数等于逗号出现的次数减 1。然后，这些字符串与另一个表合并，并执行连接查询，该表的记录条数至少要等于全体字符串中值的个数的最大值。函数 SUBSTR 和 INSTR 利用 POS 值解析每个字符串。

select iter.pos, src.name,
       substr( src.name,
        instr( src.name,',',1,iter.pos )+1,
        instr( src.name,',',1,iter.pos+1 ) -
        instr( src.name,',',1,iter.pos )-1) sub
  from (select ','||name||',' as name from v) src,
       (select rownum pos from emp) iter
 where iter.pos < length(src.name)-length(replace(src.name,','))
　
POS NAME                              SUB
--- --------------------------------- -------------
  1 ,mo,larry,curly,                  mo
  1 , tina,gina,jaunita,regina,leena,  tina
  2 ,mo,larry,curly,                  larry
  2 , tina,gina,jaunita,regina,leena, gina
  3 ,mo,larry,curly,                  curly
  3 , tina,gina,jaunita,regina,leena, jaunita
  4 , tina,gina,jaunita,regina,leena, regina
  5 , tina,gina,jaunita,regina,leena, leena

第一次调用 SUBSTR 函数中的 INSTR 函数可以确定要提取的子字符串的开始位置。第二次调用 SUBSTR 函数中的 INSTR 函数能够找到第 n 个逗号的位置（与开始位置相同）和第 n+1 个逗号的位置。上述两个值相减得到了要提取的字符串的长度。因为每个值被解析后都作为单独的行返回，只需要简单地指定 WHERE POS = n，就能筛选出第 n 个子字符串（本例中，WHERE POS = 2，因此要提取的是列表中第 2 个子字符串）。
PostgreSQL
使用内嵌视图 X 遍历每个字符串。返回的行数取决于每个字符串中包含多少个值。为了得到字符串里值的个数，我们需要计算出字符串中分隔符出现的次数，然后再加上 1。函数 SPLIT_PART 使用 POS 的值找到分隔符第 n 次出现的位置，并解析字符串提取出的名字。

select iter.pos, src.name as name1,
        split_part(src.name,',',iter.pos) as name2
   from (select id as pos from t10) iter,
        (select cast(name as text) as name from v) src
  where iter.pos <=
        length(src.name)-length(replace(src.name,',',''))+1
　
 pos |             name1              |  name2
-----+--------------------------------+---------
   1 | mo,larry,curly                 | mo
   2 | mo,larry,curly                 | larry
   3 | mo,larry,curly                 | curly
   1 | tina,gina,jaunita,regina,leena | tina
   2 | tina,gina,jaunita,regina,leena | gina
   3 | tina,gina,jaunita,regina,leena | jaunita
   4 | tina,gina,jaunita,regina,leena | regina
   5 | tina,gina,jaunita,regina,leena | leena