Recipe 6.1. Walking a String

最新推荐文章于 2023-12-09 14:41:34 发布

转载最新推荐文章于 2023-12-09 14:41:34 发布 · 117 阅读

本文介绍了一种使用SQL在没有循环操作的情况下遍历字符串的方法。通过创建笛卡尔积并结合内置的字符串解析函数，可以将字符串中的每个字符作为单独的行返回。此技巧适用于需要逐字符处理字符串的场景。

Recipe 6.1. Walking a String

Problem

You want to traverse a string to return each character as a row, but SQL lacks a loop operation. For example, you want to display the ENAME "KING" from table EMP as four rows, where each row contains just characters from "KING".

Solution

Use a Cartesian product to generate the number of rows needed to return each character of a string on its own line. Then use your DBMS's built-in string parsing function to extract the characters you are interested in (SQL Server users will use SUBSTRING instead of SUBSTR):

	
	1 select substr(e.ename,iter.pos,1) as C
	2   from (select ename from emp where ename = 'KING') e,
	3        (select id as pos from t10) iter
	4  where iter.pos <= length(e.ename)
	
	C
	-
	K
	I
	N
	G

Discussion

The key to iterating through a string's characters is to join against a table that has enough rows to produce the required number of iterations. This example uses table T10, which contains 10 rows (it has one column, ID, holding the values 1 through 10). The maximum number of rows that can be returned from this query is 10.

The following example shows the Cartesian product between E and ITER (i.e., between the specific name and the 10 rows from T10) without parsing ENAME:

	
	select ename, iter.pos
	  from (select ename from emp where ename = 'KING') e,
	       (select id as pos from t10) iter
	
	ENAME             POS
	---------- ----------
	KING                1
	KING                2
	KING                3
	KING                4
	KING                5
	KING                6
	KING                7
	KING                8
	KING                9
	KING               10

The cardinality of inline view E is 1, and the cardinality of inline view ITER is 10. The Cartesian product is then 10 rows. Generating such a product is the first step in mimicking a loop in SQL.

It is common practice to refer to table T10 as a "pivot" table.

The solution uses a WHERE clause to break out of the loop after four rows have been returned. To restrict the result set to the same number of rows as there are characters in the name, that WHERE clause specifies ITER.POS <= LENGTH(E. ENAME) as the condition:

	
	select ename, iter.pos
	  from (select ename from emp where ename = 'KING') e,
	       (select id as pos from t10) iter
	 where iter.pos <= length(e.ename)
	
	ENAME             POS
	---------- ----------
	KING                1
	KING                2
	KING                3
	KING                4

Now that you have one row for each character in E.ENAME, you can use ITER.POS as a parameter to SUBSTR, allowing you to navigate through the characters in the string. ITER.POS increments with each row, and thus each row can be made to return a successive character from E.ENAME. This is how the solution example works.

Depending on what you are trying to accomplish you may or may not need to generate a row for every single character in a string. The following query is an example of walking E.ENAME and exposing different portions (more than a single character) of the string:

	select substr(e.ename,iter.pos) a,
	       substr(e.ename,length(e.ename)-iter.pos+1) b
	  from (select ename from emp where ename = 'KING') e,
	       (select id pos from t10) iter
	 where iter.pos <= length(e.ename)

	A          B
	---------- ------
	KING       G
	ING        NG
	NG         ING
	G          KING

The most common scenarios for the recipes in this chapter involve walking the whole string to generate a row for each character in the string, or walking the string such that the number of rows generated reflects the number of particular characters or delimiters that are present in the string.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23895263/viewspace-681342/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23895263/viewspace-681342/