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.
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 4õ26 = 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:
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:
You can use console to verify this.
Suggested exercises: 106