SQL 从不固定位置提取字符串的元素,你有一个字符串,其中包含一段连续的日志数据。你想解析该字符串,并从中提取出部分信息。不过,你需要的信息并不存在于字符串的固定位置。因此,你必须借助目标信息附近的某些字符来定位并提取所需的内容。
SQL 从不固定位置提取字符串的元素 问题描述
你有一个字符串,其中包含一段连续的日志数据。你想解析该字符串,并从中提取出部分信息。不过,你需要的信息并不存在于字符串的固定位置。因此,你必须借助目标信息附近的某些字符来定位并提取所需的内容。例如,考虑下面的字符串。
你希望提取出方括号内的值,返回如下所示的结果集。
SQL 从不固定位置提取字符串的元素 解决方案
尽管不知道我们所感兴趣的字符的确切位置,但我们确定它们是被包含在方括号“[]
”中的,并且知道有 3 组这样的值。使用 Oracle 的内置函数 INSTR
找出方括号的位置。使用内置函数 SUBSTR
从字符串中提取所需要的值。视图 V
中包含了我们要解析的字符串,它的定义如下所示。(它的存在只是为了增强代码的可读性。)
SQL 从不固定位置提取字符串的元素 扩展知识
有了 Oracle 的内置函数 INSTR
,很容易就能解决本问题。由于已经知道我们感兴趣的值被“[]
”包围,并且有 3 组“[]
”,那么,本解决方案的第一步就是要使用 INSTR
在每个字符串中找出“[]
”的位置。下面的例子返回了每一行中 3 个左方括号和 3 个右方括号的确切位置。
现在,最困难的工作已经完成了。剩下的只需要把方括号的位置插入到 SUBSTR
以实现 MSG
的解析即可。你可能已经注意到上述完整的解决方案里有一些针对 INSTR
返回值的简单的数学运算,例如:+1
和–1
;这是为了确保左方括号 [
不会被返回。相较于完整的解决方案,下面给出的查询语句不包括这些 +1
和–1
的操作。注意,每一个返回值开头的字符都是左方括号。
从以上结果集中可以看到,左方括号也被返回了。你可能会想:“好吧,为 INSTR
的返回值加上 1
,这样就去掉了左方括号。为什么要减去 1
呢?”因为如果我们把加 1
的操作放回去,而不添加减 1
操作的话,右方括号就会被返回,如下所示。
现在应该很清楚了,为了确保不返回方括号,我们必须在索引开始的位置加上 1,并在索引结束的位置减去 1。