SQL 从不固定位置提取字符串的元素|极客教程

SQL 从不固定位置提取字符串的元素，你有一个字符串，其中包含一段连续的日志数据。你想解析该字符串，并从中提取出部分信息。不过，你需要的信息并不存在于字符串的固定位置。因此，你必须借助目标信息附近的某些字符来定位并提取所需的内容。

SQL 从不固定位置提取字符串的元素问题描述

你有一个字符串，其中包含一段连续的日志数据。你想解析该字符串，并从中提取出部分信息。不过，你需要的信息并不存在于字符串的固定位置。因此，你必须借助目标信息附近的某些字符来定位并提取所需的内容。例如，考虑下面的字符串。

xxxxxabc[867]xxx[-]xxxx[5309]xxxxx
xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx
call:[F_GET_ROWS( )]b1:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx
film:[non_marked]qq:[unit]tailpipe:[withabanana?]80sxxxxx

你希望提取出方括号内的值，返回如下所示的结果集。

FIRST_VAL       SECOND_VAL      LAST_VAL
--------------- --------------- ---------------
867            -                5309
11271978       4                Joe
F_GET_ROWS( )  ROSEWOOD...SIR   44400002
non_marked     unit             withabanana?

SQL 从不固定位置提取字符串的元素解决方案

尽管不知道我们所感兴趣的字符的确切位置，但我们确定它们是被包含在方括号“[]”中的，并且知道有 3 组这样的值。使用 Oracle 的内置函数 INSTR 找出方括号的位置。使用内置函数 SUBSTR 从字符串中提取所需要的值。视图 V 中包含了我们要解析的字符串，它的定义如下所示。（它的存在只是为了增强代码的可读性。）

create view V
as
select 'xxxxxabc[867]xxx[-]xxxx[5309]xxxxx' msg
  from dual
 union all
select 'xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx' msg
  from dual
 union all
select 'call:[F_GET_ROWS()]b1:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx' msg
  from dual
 union all
select 'film:[non_marked]qq:[unit]tailpipe:[withabanana?]80sxxxxx' msg
  from dual
　
 1  select substr(msg,
 2          instr(msg,'[',1,1)+1,
 3          instr(msg,']',1,1)-instr(msg,'[',1,1)-1) first_val,
 4         substr(msg,
 5          instr(msg,'[',1,2)+1,
 6          instr(msg,']',1,2)-instr(msg,'[',1,2)-1) second_val,
 7         substr(msg,
 8          instr(msg,'[',-1,1)+1,
 9          instr(msg,')',-1,1)-instr(msg,'[',-1,1]-1) last_val
10    from V

SQL 从不固定位置提取字符串的元素扩展知识

有了 Oracle 的内置函数 INSTR，很容易就能解决本问题。由于已经知道我们感兴趣的值被“[]”包围，并且有 3 组“[]”，那么，本解决方案的第一步就是要使用 INSTR 在每个字符串中找出“[]”的位置。下面的例子返回了每一行中 3 个左方括号和 3 个右方括号的确切位置。

select instr(msg,'[',1,1) “1st_[“,
       instr(msg,']',1,1) “]_1st”,
       instr(msg,'[',1,2) “2nd_[“,
       instr(msg,']',1,2) “]_2nd”,
       instr(msg,'[',-1,1) “3rd_[“,
       instr(msg,')',-1,1) “]_3rd”
  from V
　
 1st_[ ]_1st      2nd_[ ]_2nd      3rd_[ ]_3rd
------ ----- ---------- ----- ---------- -----
     9    13         17    19         24    29
    11    20         28    30         34    38
     6    19         23    38         42    51
     6    17         21    26         36    49

现在，最困难的工作已经完成了。剩下的只需要把方括号的位置插入到 SUBSTR 以实现 MSG 的解析即可。你可能已经注意到上述完整的解决方案里有一些针对 INSTR 返回值的简单的数学运算，例如：+1 和–1；这是为了确保左方括号 [ 不会被返回。相较于完整的解决方案，下面给出的查询语句不包括这些 +1 和–1 的操作。注意，每一个返回值开头的字符都是左方括号。

select substr(msg,
       instr(msg,'[',1,1],
       instr(msg,']',1,1)-instr(msg,'[',1,1]) first_val,
      substr(msg,
       instr(msg,'[',1,2],
       instr(msg,']',1,2)-instr(msg,'[',1,2]) second_val,
      substr(msg,
       instr(msg,'[',-1,1],
       instr(msg,']',-1,1)-instr(msg,'[',-1,1]) last_val
  from V
　
FIRST_VAL            SECOND_VAL      LAST_VAL
--------------- --------------- -------------
[867            [-              [5309
[11271978       [4              [Joe
[F_GET_ROWS()   [ROSEWOOD...SIR [44400002
[non_marked     [unit           [withabanana?

从以上结果集中可以看到，左方括号也被返回了。你可能会想：“好吧，为 INSTR 的返回值加上 1，这样就去掉了左方括号。为什么要减去 1 呢？”因为如果我们把加 1 的操作放回去，而不添加减 1 操作的话，右方括号就会被返回，如下所示。

select substr(msg,
       instr(msg,'[',1,1]+1,
       instr(msg,']',1,1)-instr(msg,'[',1,1)) first_val,
      substr(msg,
       instr(msg,'[',1,2]+1,
       instr(msg,']',1,2)-instr(msg,'[',1,2)) second_val,
      substr(msg,
       instr(msg,'[',-1,1]+1,
       instr(msg,')',-1,1)-instr(msg,'[',-1,1]) last_val
  from V
　
FIRST_VAL       SECOND_VAL      LAST_VAL
--------------- --------------- -------------
867]            -]              5309]
11271978]       4]              Joe]
F_GET_ROWS()]   ROSEWOOD...SIR] 44400002]
non_marked]     unit]           withabanana?]

现在应该很清楚了，为了确保不返回方括号，我们必须在索引开始的位置加上 1，并在索引结束的位置减去 1。