Проход строки 135

Проход строки Задача

Требуется обойти строку и возвратить каждый ее символ в отдельной строке таблицы, но в SQL нет операции цикла.1 Например, значение столбца ENAME (KING) таблицы EMP необходимо представить в виде четырех строк таблицы по одному символу слова KING в каждой.

Решение

Чтобы получить количество строк, необходимое для вывода каждого символа в отдельной строке, используем декартово произведение. Затем с помощью встроенной функции СУБД для синтаксического разбора извлекаем интересующие нас символы (пользователи SQL Server будут работать с функцией SUBSTRING, а не SUBSTR2):

1 select substr(e.ename,iter.pos,1) as C

2 from (select ename from emp where ename = KING) e,

3 (select id as pos from t10) iter

4 where iter.pos <= length(e.ename)

Обсуждение

Основная идея перебора символов строки - осуществить объединение с таблицей, имеющей достаточно строк, чтобы обеспечить необходимое число итераций. В данном примере используется таблица Т10, содержащая 10 строк (и один столбец, ID, в котором располагаются значения от 1 до 10). В результате этого запроса может быть возвращено максимум 10 строк.

В следующем примере показано декартово произведение представлений E и ITER (т. е. декартово произведение заданного имени и 10 строк таблицы Т10) без синтаксического разбора ENAME:

select ename, iter.pos from (select ename from emp where ename = KING) e, (select id as pos from t10) iter

Большинство СУБД поддерживают процедурные расширения, в которых есть циклы. - Примеч. науч.ред.

Кроме того, в SQL Server используется функция LEN вместо LENGTH. -Примеч. науч. ред.

Кардинальность вложенного запроса Е равна 1, а вложенного запроса ITER - 10. Таким образом, в результате декартова произведения получаем 10 строк. Формирование такого произведения - первый шаг при имитации цикла в SQL.

Обычно таблицу Т10 называют сводной таблицей.

Для выхода из цикла после возвращения четырех строк в решении используется предикат WHERE. Чтобы результирующее множество содержало столько же строк, сколько символов в имени, WHERE накладывает условие ITER.POS <= LENGTH(E.ENAME):

select ename, iter.pos from (select ename from emp where ename = KING) e, (select id as pos from t10) iter iter.pos <= length(e.ename)

where

ENAME

KING KING KING KING

Теперь, когда количество строк соответствует количеству символов E.ENAME, ITER.POS можно использовать как параметр SUBSTR, что позволит перебирать символы строки имени. Каждое последующее значение ITER.POS больше предыдущего на единицу, таким образом, можно сделать, чтобы каждая последующая строка возвращала очередной символ E.ENAME. Таков принцип работы примера решения.

В строке может выводиться разное количество символов в зависимости от поставленной задачи. Следующий запрос является примером обхода E.ENAME и вывода разных частей (более одного символа) строки:

select substr(e.ename,iter.pos) a,

substr(e.ename,length(e.ename)-iter.pos+1) b

Обсуждение

Работая с кавычками, обычно удобно рассматривать их как скобки. Если есть открывающая скобка, всегда должна быть соответствующая ей закрывающая скобка. То же самое и с кавычками. Необходимо помнить, что число кавычек в любой строке должно быть четным. Чтобы вставить в строку одну кавычку, мы должны использовать две кавычки:

select apples core, apples core,

case when is null then 0 else 1 end from t1

APPLESCORE APPLE SCOR CASEWHEN ISNULLTHEN0ELSE1END apples core apples core 0

from (select ename from emp where ename = KING) e, (select id pos from t10) iter where iter.pos <= length(e.ename)

KING G ING NG NG ING

G KING

Чаще всего в рецептах данной главы используются сценарии обхода всей строки и вывода каждого ее символа в отдельной строке таблицы или обхода строки таким образом, чтобы число сгенерированных строк таблицы отражало количество символов или разделителей в строке.

Как вставить кавычки в строковые литералы Задача

Требуется вставить кавычки в строковые литералы. Хотелось бы с помощью SQL получить результат, подобный приведенному ниже:

QMARKS

gday mate beavers teeth

Решение

Следующие три выражения SELECT представляют разные способы создания кавычек: как в середине строки, так и отдельных:

1 select gday mate qmarks from t1 union all

2 select beavers teeth from t1 union all

3 select from t1