利用辅助表格进行特定格式字符串的解析

文档选项 未显示需要 JavaScript 的文档选项

级别： 初级

寇远超上海网元计算机系统有限公司

2003 年 11 月 01 日

本文将采用一种纯 SQL 的解决方案即利用辅助表格编写标准的 SQL 语句来完成标准格式字符串的解析，并利用这种方式完成一个衍生应用。

前言

在数据库程序开发中，经常遇到的一种情形是特定格式的字符串存储了应以行方式表现的数据集合。下面的查询结果是这种情形的一个样本。

清单 1. 查询 DB2 系统目录视图 SYSCAT.CHECKS
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.

可以很容易地观察出在上面的结果集合中。字符串存储了以逗号为分割符号的多个元素。为了较为方便地使用这些元素，常常需要对字符串进一步的解析和处理，从而转变为如下的表现形式。

注：仅解析"SYSIBM","SYSFUN","SYSPROC","STOLZE"
FUNC_PATH
------------------------------------------
"SYSIBM"
"SYSFUN"
"SYSPROC"
"STOLZE"

在传统过程性开发语言中，例如 C 或者 Java 语言中，如果不利用系统函数（类）而是自己编写字符串函数（类）的话，其原理通常是利用 FOR 循环或递归迭代方式。

利用同样的原理，在数据库中亦可利用扩展 SQL 编写函数来完成字符串解析，不能说这是一种很理想的解决方案。相对 SQL 的编写/调试来说，函数的编写/调试是较为复杂的一种技术。而且由于函数以及函数内采用的特定技术的原因，这种方案不能很好地在不同的数据库环境中移植。本文将采用一种纯 SQL 的解决方案即利用辅助表格编写标准的 SQL 语句来完成标准格式字符串的解析，并利用这种方式完成一个衍生应用。

回页首

场景准备

在进一步阐述新方式之前，我们要建立简单的场景：即样本表格和用于观察及测试的数据。这个清单及其数据被用于下面的所有示例。

清单 2. 创建表并插入示例数据
CREATE TABLE strings (
   id   INTEGER       NOT NULL  PRIMARY KEY,
   str  VARCHAR(128)  NOT NULL
);
INSERT INTO strings VALUES ( 1,'ab,c,d,123' ),
       ( 2,'123,456789,abc,123' ),( 3,'a,b,c,d' ),
       ( 4,'string' );
SELECT * FROM strings;
ID          STR
----------- ----------------------
          1 ab,c,d,123
          2 123,456789,abc,123
          3 a,b,c,d
          4 string
  4 record(s) selected.

可以确定的是，对字符串进行解析的前提是可以获取字符串中的所有字符，在区分出字符分割符位置的基础之上进行多个元素的界定。下面的表格是对编号为 1 的字符串进行手工字符解析的结果。

列表 1：对编号为 1 的字符串进行解析

观察列表 1 可以发现，字符可以由字符串和字符位置推导出来。但仅仅通过表格 STRINGS 是无法提供"字符串"和"字符位置"这样的结果集合的。想像一下，如果存在一个存储从 1 到字符串长度的数据序列表格，那么可以把"字符串"和"字符位置"这样的结果集视为字符串和数据序列表格的一个笛卡尔集。

这个序列表格就是标题中所述的辅助表格，在它的帮助之下，对每一个字符进行解析都成了可能。对辅助表格的要求是存储足够大，内容是从一开始的连续数据序列。下面是建立这个辅助表格的脚本。

清单 3 创建序列表并插入示例数据
CREATE TABLE numSerial (
   id   INTEGER       NOT NULL  PRIMARY KEY
)
	注：根据需要插入足够的数据序列
insert into numSerial(id)
select rn from (
	select row_number() over() as rn 
	from sysCat.tables 
)a where rn<=100;
SELECT * FROM numSerial;
ID         
----------- 
          1 
          2 
          3 
          . . .
	100
 100 record(s) selected.

回页首

技术实现

在上面建立的两个表格和数据的基础之上，我们将针对这种新方式进行一系列试验，从而逐步实现本文所提出的命题。

• 试验 1：检索出字符串中的每一个字符及位置

  SELECT str,numSerial.id as chrIndex,substr(str,numSerial.id,1) as theChr From strings,numSerial --对字符串的搜索进行长度限定。 Where numserial.id<=length(strings.str) --为方便观察解析数据。仅处理第一行数据 And strings.id=1 STR CHRINDEX THECHR ---------- ----------- ----- ab,c,d,123 1 a ab,c,d,123 2 b ab,c,d,123 3 , ab,c,d,123 4 c ab,c,d,123 5 , ab,c,d,123 6 d ab,c,d,123 7 , ab,c,d,123 8 1 ab,c,d,123 9 2 ab,c,d,123 10 3 10 record(s) selected. 注：通过辅助表格 numSerial，STR 列在结果集合中出现 length(str) 次 根据字符串和字符串位置，利用字符串截取函数确定出该位置的字符（串）。

  
  

• 试验 2： 确定字符串中的分割符位置

  SELECT str,numserial.id as chrIndex From strings,numSerial --对字符串的搜索进行长度限定。 Where numserial.id<=length(strings.str) And strings.id=1 and substr(str,numSerial.id,1) =',' STR CHRINDEX ---------- -------------------------- ab,c,d,123 3 ab,c,d,123 5 ab,c,d,123 7 3 record(s) selected. 注：确定各个分割符的位置是进行字符串多个元素分割的前提

  
  

• 试验 3 ：获取分割符位置和该位置的下一个分割符位置

  select strings.id strId,numSerial.id lIndex,locate(',',str,numSerial.id+1) rIndex from strings,numSerial --对字符串的搜索进行长度限定。 where numSerial.id<=length(strings.str) --屏蔽非分割符位置 and substr(str,numSerial.id,1)=',' and strings.id=1 STR LINDEX RINDEX ---------- ----------- ------------ ab,c,d,123 3 5 ab,c,d,123 5 7 ab,c,d,123 7 0 3 record(s) selected. 注：该脚本用于形成字符串内元素的边界。 注意到最后一个分割符的下一个分割符位置即字符串终点位置为 0。

  
  

• 试验 4：根据分割符位置和字符串起始终点位置形成所有元素的边界。

  select str, --用 Case 逻辑进行头尾处理 case n.id when 1 then 1 else n.id+1 end chrIndex, case locate(',',str,n.id+1) when 0 then length(str)+1 else locate(',',str,n.id+1) end rIndex from strings s,numSerial n --对字符串的搜索进行长度限定。 where n.id<=length(s.str) --屏蔽非分割符位置,利用 or 逻辑将头位置加入 and (substr(str,n.id,1)=',' or n.id=1) and s.id=1 STR CHRINDEX RINDEX ---------- --------- ----- ab,c,d,123 1 3 ab,c,d,123 4 5 ab,c,d,123 6 7 ab,c,d,123 8 11 4 record(s) selected. 注：字符串中第一个元素无前切分符位置。最后一个元素无后切分符位置.

  
  

• 试验 5：完成样本表格内所有字符串的切割。

  select str,substr(str,chrIndex,rIndex-chrIndex) item from( select s.id,str,case n.id when 1 then 1 else n.id+1 end chrIndex, case locate(',',str,n.id+1) when 0 then length(str)+1 else locate(',',str,n.id+1) end rIndex from strings s,numSerial n --对字符串的搜索进行长度限定。 where n.id<=length(s.str) --屏蔽非分割符位置并利用or逻辑设置头位置 and (substr(str,n.id,1)=',' or n.id=1) )strings order by id STR ITEM ------------- ---------- ab,c,d,123 ab ab,c,d,123 c ab,c,d,123 d ab,c,d,123 123 123,456789,abc,123 123 123,456789,abc,123 456789 123,456789,abc,123 abc 123,456789,abc,123 123 a,b,c,d a a,b,c,d b a,b,c,d c a,b,c,d d string string 12 record(s) selected. 注：切分完成。

  
  

通过上面一系列的案例，借助于辅助表格，终于可以使用标准 SQL 的方式完成特定格式字符串的解析。即使在不存在物理辅助表格的情况下，利用导出表也可以很容易地完成这种功能。

回页首

衍生应用案例

还可以列举出利用辅助表格来进行相似性比较的应用，即如果两个字符串中有连续的若干个（用户定义）字符可以匹配的话，可以认为这两个字符串具有相似性。LIKE 谓词是 SQL 语句中比较常用的一个功能。但对于这样一种应用，很难直接应用。借助于辅助表格，可以很容易地完成这个应用。

下面是这个应用的场景和解决方案。在这个应用中，我们查找样本表格 1 中的字符串和样本表格 2 中的字符串有 6 个字符匹配的记录。

--样本表格1
Create table strList1(
	Str	varchar(100)
)
insert into strList1 values('abcdefgh'),('aaabbbcc'),('012345667')；
 --样本表格1
Create table strList2(
	Str	varchar(100)
)
insert into strList2 values('xbcdefgx'),( 'aacbxbcc'),( '12345678')；
--查找具有相似性的字符串和相应的子字符串。
select  a.str str1,b.str str2,item  from (
	--该子查询列出在表格strList1中长度为 6 所有的子字符串
	select str,substr(str,numSerial.id,6) item 
	from (select str str,id from strList1)strList1,numSerial
	where numSerial.id<=length(str)-6+1
)a,strList2 b
--检测 STRLIST1 中的子字符串是否在 STRLIST 中存在
where locate(item,b.str)<>0
STR1		STR2		ITEM
-----	----		------
abcdefgh	xbcdefgx	bcdefg	
012345667	12345678	123456	
2 record(s) selected.
注：具有相似性的字符串可能有存在多个子字符。

回页首

结束语

本文展示了在辅助表格即连续数据序列帮助下，利用 SQL 语句而不是函数的方式进行字符串解析的方案，并利用这种方式解决了一个衍生的应用。因为这是一种标准的 SQL 解决方案，可以根据需要很容易地在多种数据库环境下将之改写。

关于作者

		寇远超是上海网元计算机系统有限公司的数据库主管，主要从事在多种数据库系统环境中进行数据库开发、设计以及性能调整方面的工作，专注于数据库技术解决方案。

对本文的评价

太差！ (1) 需提高 (2) 一般；尚可 (3) 好文章 (4) 真棒！(5) 建议？

回页首