解析 SQL 中的字符串

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.

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 < 10000 AND
-- terminate if there are no further delimiters
-- remaining
                  LOCATE(',', string, index+1) <> 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
;

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.

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 )
;

SELECT VARCHAR(elem, 20)
FROM   TABLE ( elements('abc, def, ghi, 123') ) AS t(elem);
1
--------------------
abc
 def
 ghi
 123
  4 record(s) selected.

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' );
SELECT * FROM strings;
ID          STR
----------- -------------------------------------------------
          1 abc, def, ghi, 123
          2 123,456789,abc,123
          3 a,b,c,a,b,c,a
          4 string
  4 record(s) selected.

SELECT COUNT(DISTINCT VARCHAR(RTRIM(LTRIM(elem)), 1000))
FROM   TABLE ( elements('abc, def,abc ,ghi') ) AS x(elem);
1
-----------
          3
  1 record(s) selected.
SELECT id, COUNT(DISTINCT VARCHAR(RTRIM(LTRIM(elem)), 20))
FROM   strings, TABLE ( elements(str) ) AS t(elem)
GROUP BY id;
ID          2
----------- -----------
          1           4
          2           3
          3           3
          4           1
  4 record(s) selected.

SELECT id, ( SELECT COUNT(*)
             FROM   TABLE ( elements(str) ) AS x(elem)
             WHERE  VARCHAR(RTRIM(LTRIM(elem)), 100) = '123' )
FROM   strings;
ID          2
----------- -----------
          1           1
          2           2
          3           0
          4           0
  4 record(s) selected.