New sql features

Common table expressions (CTE)

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

WITH NM(name) AS
(SELECT 'ALfa and omegA' AS name
UNION ALL SELECT 'alfa beta gamma    zeta'
UNION ALL SELECT 'KSI PSI'
UNION ALL SELECT 'delta'
),
Ascii_code AS(
SELECT  ASCII('A') AS code
),
Repl(name, code, rep) AS
(SELECT name, code, REPLACE(UPPER(LEFT(name, 1)) + 
LOWER(SUBSTRING(name, 2, LEN(name) - 1)),' '+CHAR(code),' '+CHAR(code)) rep 
FROM Ascii_code, NM
) 
SELECT name, rep FROM Repl;

WITH NM(name) AS (SELECT 'ALfa and omegA' AS name UNION ALL SELECT 'alfa beta gamma    zeta' UNION ALL SELECT 'KSI PSI' UNION ALL SELECT 'delta' ), Ascii_code AS( SELECT  ASCII('A') AS code ), Repl(name, code, rep) AS (SELECT name, code, REPLACE(UPPER(LEFT(name, 1)) + LOWER(SUBSTRING(name, 2, LEN(name) - 1)),' '+CHAR(code),' '+CHAR(code)) rep FROM Ascii_code, NM ) SELECT name, rep FROM Repl;

    Scheme

No scheme

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:

Console

Execute

WITH NM(name) AS
(SELECT 'ALfa and omegA' AS name
UNION ALL SELECT 'alfa beta gamma    zeta'
UNION ALL SELECT 'KSI PSI'
UNION ALL SELECT 'delta'
),
Ascii_code AS(
SELECT  ASCII('A') AS code
),
Repl(name, code, rep) AS
(SELECT name, code, REPLACE(UPPER(LEFT(name, 1)) + 
LOWER(SUBSTRING(name, 2, LEN(name) - 1)),' '+CHAR(code),' '+CHAR(code)) rep 
FROM Ascii_code, NM
UNION ALL
SELECT name, code+1 code, 
REPLACE(rep,' ' + CHAR(code+1), ' ' + char(code + 1)) rep FROM Repl
WHERE code < ASCII('Z')
)
SELECT name, rep FROM Repl WHERE code=ASCII('Z');

WITH NM(name) AS (SELECT 'ALfa and omegA' AS name UNION ALL SELECT 'alfa beta gamma    zeta' UNION ALL SELECT 'KSI PSI' UNION ALL SELECT 'delta' ), Ascii_code AS( SELECT  ASCII('A') AS code ), Repl(name, code, rep) AS (SELECT name, code, REPLACE(UPPER(LEFT(name, 1)) + LOWER(SUBSTRING(name, 2, LEN(name) - 1)),' '+CHAR(code),' '+CHAR(code)) rep FROM Ascii_code, NM UNION ALL SELECT name, code+1 code, REPLACE(rep,' ' + CHAR(code+1), ' ' + char(code + 1)) rep FROM Repl WHERE code < ASCII('Z') ) SELECT name, rep FROM Repl WHERE code=ASCII('Z');

    Scheme

No scheme

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:

SELECT INITCAP(name) 
  FROM
   (SELECT 'ALfa and omegA' AS name
     UNION ALL SELECT 'alfa beta gamma zeta'
     UNION ALL SELECT 'KSI PSI'
     UNION ALL SELECT 'delta'
 ) X;

You can use console to verify this.

Content:

About generation of number sequences in SQL Server