解析 sql 中的字符串.htm

解析 SQL 中的字符串

            width=100> </TD></TR>
        <TR class=ddb1 vAlign=top>
          <TD class=dwsmall colSpan=3><A 
            href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#3">使用方案</A></TD></TR>
        <TR class=ddb1>
          <TD colSpan=3><IMG alt="" border=0 height=8 
            src="C:\Documents and Settings\hjh1\桌面\db2 语法参考\解析 SQL 中的字符串.files\c(1).gif" 
            width=100> </TD></TR>
        <TR class=ddb1 vAlign=top>
          <TD class=dwsmall colSpan=3><A 
            href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#4">结束语</A></TD></TR>
        <TR class=ddb1>
          <TD colSpan=3><IMG alt="" border=0 height=8 
            src="C:\Documents and Settings\hjh1\桌面\db2 语法参考\解析 SQL 中的字符串.files\c(1).gif" 
            width=100> </TD></TR>
        <TR class=ddb1 vAlign=top>
          <TD class=dwsmall colSpan=3><A 
            href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#5">参考资料</A></TD></TR>
        <TR class=ddb1>
          <TD colSpan=3><IMG alt="" border=0 height=8 
            src="C:\Documents and Settings\hjh1\桌面\db2 语法参考\解析 SQL 中的字符串.files\c(1).gif" 
            width=100> </TD></TR>
        <TR class=ddb1 vAlign=top>
          <TD class=dwsmall colSpan=3><A 
            href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#6">免责声明</A></TD></TR>
        <TR class=ddb1>
          <TD colSpan=3><IMG alt="" border=0 height=8 
            src="C:\Documents and Settings\hjh1\桌面\db2 语法参考\解析 SQL 中的字符串.files\c(1).gif" 
            width=100> </TD></TR>
        <TR class=ddb1 vAlign=top>
          <TD bgColor=#ffffcc class=dwsmall colSpan=3><SUP><A 
            name=fn1>1</A></SUP>本文中的所有 SQL 语句都采用分号作为语句的结束符。如果从 DB2 命令行执行语句，则必须用 
            <CODE><B>db2 -t</B></CODE> 启动 DB2 命令行。如果使用 DB2 命令中心时，可以在 <B>Tools 
            -&gt; Tools Settings -&gt; Use statement termination character</B> 
            菜单中调整语句结束符。另一种方法是，可以更改要执行语句的结束符。 </TD></TR></TBODY></TABLE><!-- sidebar table of contents ends --><!-- comments #6: html content of the paper
<p><em>abstract of the article</em></p>

<p>text of the paper</p> 

<p>text of paper that includes a product or resource that is referenced in the Resources section (now in parentheses include a link to the <a href="#resources">Resources</a> section)</p> 

--><!-- Sections  begins --><A id=0 name=0></A>
      <P><STRONG class=subhead>简介</STRONG><BR></P>
      <P>客户经常遇到的一种情形是，需要处理在由 DB2 
      通用数据库（UDB）管理的数据库中存储的字符串。这些字符串包含某些形式的、要进一步解析和分析的并置数据。本文显示了如何解析这些数据，并展示了对这些结果进行后续处理的一些可能性。</P>
      <P>为进一步阐明该方案，我们假定希望处理类似于在系统目录视图 SYSCAT.CHECKS 的 FUNC_PATH 
      列中可以访问到的字符串。该列列出了在创建检查约束时所使用的函数路径。<A 
      href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#listing1">清单 
      1</A> 显示了该列中的一些样本内容。<SUP><A 
      href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#fn1">1</A></SUP></P>
      <P class=caption><A name=listing1>清单 1. 查询 DB2 系统目录视图 
SYSCAT.CHECKS</A></P><PRE class=code>SELECT func_path FROM syscat.checks;

FUNC_PATH
--------------------------------------------------------
"SYSIBM","SYSFUN","SYSPROC","STOLZE"
"SYSIBM","SYSFUN","SYSPROC","MYSCHEMA"
"SYSIBM","SYSFUN","SYSPROC"
"SYSIBM","SYSFUN","SYSPROC"

  4 record(s) selected.
</PRE>
      <P>正如您所见，每个字符串包含了由逗号分隔的几个元素，这个逗号就是定界符。现在我描述的解析将检索每个字符串中的单个元素。请注意，只要略微地改变一下定界符本身，就可以用完全相同的方法来处理使用其它定界符的字符串。</P>
      <P>有几种方式可以实现所描述的解析。显然，用 Java 或 C/C++ 
      实现的用户定义的表函数可以完成此任务。这样的函数将每个字符串作为输入参数，然后分行返回每个元素。虽然用 C/C++ 
      实现的函数可以提供最佳性能，但可能并不要求使用外部编程语言，因为这会对开发环境增加一些要求；例如，必须为每个目标系统部署实现用户定义的函数（UDF）的库。作为编程语言的 
      SQL 已经能够处理该特定任务，并且数据库用户可以使用 SQL。</P><A id=1 name=1></A>
      <P><STRONG class=subhead>迭代字符串</STRONG><BR></P>
      <P>解析字符串的基本任务是迭代字符串中的所有字符。SQL 提供了两种方式来完成此迭代：</P>
      <UL class=standard>
        <LI>FOR 循环 
        <LI>递归查询 </LI></UL>
      <P>DB2 V8.1 中添加了用于分区数据库（ESE）的内联 SQL PL。在 V8.1 中可以使用属于内联 SQL PL 的循环，但对于 
      V7.2，还没有用于分区数据库的循环。此外，如果使用循环，则更难以在表中表示字符串中被抽取的元素，尽管这种表示会简化 SQL 
      中的进一步处理。所以，本文将着重探讨基于递归查询的常规解决方案。</P>
      <P>递归查询中的迭代将用于查找给定字符串中的所有定界符。根据定界符的位置，就可以抽取单个元素的子字符串。为了简化这些步骤，我们使用 SQL 
      函数来执行要执行的每步任务。<A 
      href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#listing2">清单 
      2</A> 
      中显示的第一个函数将一个字符串作为输入参数，然后查找字符串中的所有定界符。该函数返回一些以升序方式表示的数字，这些数字用于标识每个定界符，另外还返回字符串中每个定界符的位置（索引）。用具有两列的表的形式返回标识符／位置对。</P>
      <P class=caption><A name=listing2>清单 2. 返回所有定界符的标识符和索引的函数</A></P><PRE class=code>CREATE FUNCTION elemIdx ( string CLOB(64K) )
   RETURNS TABLE ( ordinal INTEGER, index INTEGER )
   LANGUAGE SQL
   DETERMINISTIC
   NO EXTERNAL ACTION
   CONTAINS SQL
   RETURN
      WITH t(ordinal, index) AS
         ( VALUES ( 0, 0 )
           UNION ALL
           SELECT ordinal+1, COALESCE(NULLIF(
                  -- find the next delimiter ","
                     LOCATE(',', string, index+1), 0),
                     LENGTH(string)+1)
           FROM   t
                  -- to prevent a warning condition for infinite
                  -- recursions, we add the explicit upper
                  -- boundary for the "ordinal" values
           WHERE  ordinal &lt; 10000 AND
-- terminate if there are no further delimiters
-- remaining
                  LOCATE(',', string, index+1) &lt;&gt; 0 )
      SELECT ordinal, index
      FROM   t
      UNION ALL
      -- add indicator for the end of the string
      SELECT MAX(ordinal)+1, LENGTH(string)+1
      FROM   t
;
</PRE>
      <P>注：该函数不仅返回每个定界符的索引，而且还添加了两行，一行代表索引 
      0（零），它表示字符串的开始；另一行表示字符串的结束。这样就可以方便地抽取第一个和最后一个元素。</P>
      <P><A 
      href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#listing3">清单 
      3</A> 显示了对于字符串“abc, def, ghi, 123”该函数的用法及其结果。</P>
      <P class=caption><A name=listing3>清单 3. 函数 elemIdx() 的样本输出</A></P><PRE class=code>SELECT *
FROM TABLE ( elemIdx('abc, def, ghi, 123') ) AS t
ORDER BY 1;

ORDINAL     INDEX
----------- -----------
          0           0
          1           4
          2           9
          3          14
          4          19

  5 record(s) selected.
</PRE>
      <P>这些结果显示，该字符串有 19 个字符（索引 4），并且在位置 4、9 和 14 
      处可以找到用于定界的逗号。正如前面所提到的，第一行只表示字符串的开始。</P><A id=2 name=2></A>
      <P><STRONG class=subhead>抽取子字符串</STRONG><BR></P>
      <P>使用由函数 <CODE>elemIdx()</CODE> 
      返回的索引信息，现在可以抽取字符串中的元素。为达到此目的，先确定索引，然后将这些索引用作 DB2 内置函数 SUBSTR 的参数。<A 
      href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#listing4">清单 
      4</A> 显示了该函数的模样。</P>
      <P class=caption><A name=listing4>清单 4. 返回单个元素的函数</A></P><PRE class=code>CREATE FUNCTION elements ( string CLOB(64K) )
   RETURNS TABLE ( elements CLOB(64K) )
   LANGUAGE SQL
   DETERMINISTIC
   NO EXTERNAL ACTION
   CONTAINS SQL
   RETURN
      WITH t(ordinal, index) AS
         ( SELECT ordinal, index
           FROM   TABLE ( elemIdx(string) ) AS x )
      SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1)
      -- the join below makes sure that we have the lower and
      -- upper index where we can find each of the ',' delimiters
      -- that are separating the elements.  (For this, we exploit
      -- the additional indexes pointing to the beginning and end
      -- of the string.)
      FROM   t AS t1 JOIN t AS t2 ON
                ( t2.ordinal = t1.ordinal+1 )
;
</PRE>
      <P>连接条件确保为每个元素组合了正确的上界和下界。然后，下界确定元素的起点，上界确定终点。使用上界和下界，通过简单的减法，可以计算 
      SUBSTR() 函数需要要抽取的字符串长度。现在，再次使用<A 
      href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#listing3">清单 
      3</A> 中的同一个字符串。<A 
      href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#listing5">清单 
      5</A> 显示了一条样本查询，该查询从输入字符串抽取单个元素。</P>
      <P class=caption><A name=listing5>清单 5. 函数 elements() 的样本输出</A></P><PRE class=code>SELECT VARCHAR(elem, 20)
FROM   TABLE ( elements('abc, def, ghi, 123') ) AS t(elem);


1
--------------------
abc
 def
 ghi
 123

  4 record(s) selected.
</PRE>
      <P>正如您所见，所有元素都分行返回。这里保留了每个元素中的空格。</P><A id=3 name=3></A>
      <P><STRONG class=subhead>使用方案</STRONG><BR></P>
      <P>为结束本文，我提供了一些样本查询，展示了在您的查询中如何包含 <CODE>elements()</CODE> 
      函数的结果，从而真正地执行所需的字符串分析，进而达到最初目的。</P>
      <P>这些示例都会使用<A 
      href="http://www-900.ibm.com/developerWorks/cn/dmdd/library/techarticles/0303stolze/0303stolze1.shtml#listing6">清单 
      6</A> 中所显示的表和数据。在创建上面所描述的两个函数之后，可以执行这些查询，您会得到本节中所显示的结果。</P>
      <P class=caption><A name=listing6>清单 6. 创建表并插入示例数据</A></P><PRE class=code>CREATE TABLE strings (
   id   INTEGER       NOT NULL  PRIMARY KEY,
   str  VARCHAR(128)  NOT NULL
);

INSERT
INTO   strings
VALUES ( 1, 'abc, def, ghi, 123' ),
       ( 2, '123,456789,abc,123' ),
       ( 3, 'a,b,c,a,b,c,a' ),
       ( 4, 'string' );