Recipe6.1.Walking a String

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.