Новое в стандарте и реализациях языка SQL

Общие табличные выражения (CTE)

Сначала напишем два простых  CTE (общее табличное выражение) позволяет в рамках запроса задать таблицу, на которую можно многократно ссылаться.CTE, которые формируют наш тестовый пример и определяют ASCII-код первой буквы алфавита (A) - не писать же константу. :-) Далее последует анкорная часть, которая выполняет ранее описанную операцию приведения всего текста к нижнему регистру с заглавной первой буквой. Здесь же выполним замену символа с кодом code и предшествующим ему пробелом на... него же. Пусть вас не смущает такая, казалось бы, бесполезная замена. Дело в том, что для регистронезависимых баз данных символы 'a' и 'A' не различаются. Давайте пока на этом остановимся и посмотрим результат.

Консоль

Выполнить

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;

    Схема

Нет схемы

Добавим, наконец, рекурсивную часть, в которой мы выполним замену буквы с кодом code+1. Рекурсия будет продолжаться до тех пор, пока не будет нарушено условие code < ASCII('Z'), т.е. пока мы не переберем все буквы.

Что же мы получим на выходе? К строкам, которые были получены в результате выполнения анкорной части, на каждой итерации будут добавлены (UNION ALL) те же строки с заменой очередной буквы. Отметим большой объем результата при использовании данного метода; в нашем случае это 4х26 = 104 строки. Из этого множества строк нас интересуют только те, которые получены в результате последней итерации, т.е. когда были выполнены все замены. Этой последней итерации соответствует условие 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');

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');

    Схема

Нет схемы

Я хотел бы предостеречь вас от чрезмерного увлечения рекурсивными CTE, поскольку они зачастую проигрывают в производительности "традиционным" методам. Я не буду далеко ходить за примерами и сравню два представленных здесь метода. Увеличив количество обрабатываемых строк до 10000, я получил такое время использования CPU:

метод на основе REPLACE: 842 ms

рекурсивный метод: 6615 ms

Безусловно, есть задачи, которые нельзя решить непроцедурно в рамках стандарта SQL-92. В этих случаях использование рекурсивных CTE вполне обоснованно. В остальных случаях я бы рекомендовал выполнять тесты производительности для альтернативных решений.

Кстати, в Oracle и PostgreSQL есть встроенная функция INITCAP, которая решает данную задачу:

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;

Вы можете использовать консоль, чтобы убедиться в этом.

Содержание:

О генерации числовых последовательностей в SQL Server