loading..
   English
19:58

Recursive CTE page 3

First, write the two prime  CTE (common table expression) allows to determine a table in framework of a query for multiple referencings.CTE, which form our test case and determine the ASCII-code of the first letters of the alphabet (A) (constant - not our way :-)).

Then followed anchor part, which performs the previously described operation of bringing the whole text to lowercase with uppercase first letter. Here, the replacement of character with the code code and preceded by blank space on same character. Do not be confused by such a seemingly useless replacement. The fact is that for case-insensitive databases characters 'a' and 'A' did not differ. Let's stop and see the result.

Console
Execute
  1. WITH NM(name) AS
  2. (SELECT 'ALfa and omegA' AS name
  3. UNION ALL SELECT 'alfa beta gamma zeta'
  4. UNION ALL SELECT 'KSI PSI'
  5. UNION ALL SELECT 'delta'
  6. ),
  7. Ascii_code AS(
  8. SELECT ASCII('A') AS code
  9. ),
  10. Repl(name, code, rep) AS
  11. (SELECT name, code, REPLACE(UPPER(LEFT(name, 1)) +
  12. LOWER(SUBSTRING(name, 2, LEN(name) - 1)),' '+CHAR(code),' '+CHAR(code)) rep
  13. FROM Ascii_code, NM
  14. )
  15. SELECT name, rep FROM Repl;

 

name rep
ALfa and omegA Alfa And omega
alfa beta gamma zeta Alfa beta gamma zeta
KSI PSI Ksi psi
delta Delta

We add, finally, the recursive part, which we will replace the chars with the code code +1. Recursion will continue as long as there is no violation of the condition code <ASCII ('Z'), i.e. until we will go all the characters. What do we get in output? The same rows will be added (UNION ALL) with the replacement of the next char, to rows, which were obtained as a result of anchor part, on each iteration. Note large amount of results using this method; in our case 426 = 104 rows. From this set of rows we are interested in only those that result from the last iteration, i.e. when they were made all substitutions. This latest iteration corresponds to the condition code = ASCII ('Z'), which is used in the final query:

Console
Execute
  1. WITH NM(name) AS
  2. (SELECT 'ALfa and omegA' AS name
  3. UNION ALL SELECT 'alfa beta gamma zeta'
  4. UNION ALL SELECT 'KSI PSI'
  5. UNION ALL SELECT 'delta'
  6. ),
  7. Ascii_code AS(
  8. SELECT ASCII('A') AS code
  9. ),
  10. Repl(name, code, rep) AS
  11. (SELECT name, code, REPLACE(UPPER(LEFT(name, 1)) +
  12. LOWER(SUBSTRING(name, 2, LEN(name) - 1)),' '+CHAR(code),' '+CHAR(code)) rep
  13. FROM Ascii_code, NM
  14. UNION ALL
  15. SELECT name, code+1 code,
  16. REPLACE(rep,' ' + CHAR(code+1), ' ' + char(code + 1)) rep FROM Repl
  17. WHERE code < ASCII('Z')
  18. )
  19. SELECT name, rep FROM Repl WHERE code=ASCII('Z');

I would like to warn you against excessive using of recursive CTE, as they often lose in productivity "traditional" methods. I'm not going to go far for examples and compare the method presented here. By increasing the number of processed rows to 10000 I got a following CPU utilization time:

the method based on REPLACE: 842 ms

recursive method: 6615 ms

Certainly there are tasks that can not be solved nonprocedural within the framework SQL-92 standard. In these cases, the use of recursive CTE is rightly so. In other cases, I would recommend to carry out performance tests to alternative solutions.

By the way, PostgreSQL and Oracle have a built-in function - INITCAP - which solves the problem we consider:

  1. SELECT INITCAP(name)
  2. FROM
  3. (SELECT 'ALfa and omegA' AS name
  4. UNION ALL SELECT 'alfa beta gamma zeta'
  5. UNION ALL SELECT 'KSI PSI'
  6. UNION ALL SELECT 'delta'
  7. ) X;

You can use console to verify this.

Suggested exercises: 106

Bookmark and Share
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100