How to perform loop operation in SQL?

Staff Table

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 RAHUL ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 RAJ PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAM CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 ARHAM ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

T10 Pivot Table

The pivot tables are a useful shortcut when we need to create a series of rows to facilitate a query.

T10
1
2
3
4
5
6
7
8
9
10

Since SQL doesn’t provide a loop operation and you want to traverse a string to return each character as a row. For instance, you wish to display the three rows of the name “ALI” from the Staff table, each row containing only the characters from ALI.

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 and DATALENGTH instead of LENGTH):

SELECT substr(s.ename,iter.pos,1) as C
FROM (SELECT ename FROM Staff WHERE ename = 'ALI') s,
(SELECT id as pos FROM T10) iter
WHERE iter.pos <= length(s.ename)

Output

C
A
L
I

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 s and Iter (i.e., between the specific name and the 10 rows from T10) without parsing ENAME:

SELECT substr(s.ename,iter.pos,1) as C
FROM (SELECT ename FROM Staff WHERE ename = 'ALI') s,
(SELECT id as pos FROM T10) iter

The cardinality of inline view s 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.

The solution uses a WHERE clause to break out of the loop after three 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(s. ename) as the condition:

SELECT substr(s.ename,iter.pos,1) as C
FROM (SELECT ename FROM Staff WHERE ename = 'ALI') s,
(SELECT id as pos FROM T10) iter
WHERE iter.pos <= length(s.ename)

Now that you have one row for each character in s.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 s.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.