ADAMS M ADAMS S

Представление также упорядочивает результаты по столбцу ENAME и по столбцу С в алфавитном порядке. Вложенный запрос X (строки 15-18) возвращает имена и символы, полученные в представлении V, число раз, сколько каждый символ встречается в каждом имени, и его позицию (в алфавитном порядке):

ename c cnt pos

ADAMS A 2 1

ADAMS A 2 1

ADAMS D 1 3

ADAMS M 1 4

ADAMS S 1 5

Дополнительные столбцы CNT и POS, возвращенные вложенным запросом Х, исключительно важны для решения. POS используется для ранжирования каждого символа, а CNT - для определения того, сколько раз символ встречается в данном имени. Окончательный шаг - определить позицию и реконструировать имя. Как видите, каждое выражение CASE, на самом деле, является двумя выражениями CASE. Сначала выясняется, не встречается ли данный символ в имени более одного раза. Если это так, то возвращается не один символ, а строка, в которой данный символ повторяется столько раз, сколько указано значением столбца CNT. Агрегатная функция MAX обеспечивает вывод по одной строке для каждого имени.

Выявление строк, которые могут быть интерпретированы как числа

Задача

Имеется столбец символьного типа. К несчастью, в строках содержатся и числовые, и символьные данные. Рассмотрим представление V:

create view V as

select replace(mixed, ,) as mixed

from (

select substr(ename,1,2)

cast(deptno as char(4)) substr(ename,3,2) as mixed

from emp

where deptno = 10

union all

select cast(empno as char(4)) as mixed

from emp

where deptno = 20

union all

CL10AR KI10NG MI10LL

7369

7566

7788

7876 7902 ALLEN WARD

MARTIN BLAKE TURNER JAMES

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

MIXED

10 10 10

7369

7566

77788786 7902

Решение

Для работы со строками и отдельными символами удобно использовать функции REPLACE и TRANSLATE. Суть решения - заменить все числа одним символом, что позволит затем без труда выделить и выбрать каждое число, ссылаясь лишь на один символ.

Для выбора чисел из строк используются функции TRANSLATE, REPLACE и POSSTR. Функция CAST является обязательной в представ-

select ename as mixed

from emp where deptno = 30 ) x

select * from v MIXED

лении V, поскольку в противном случае представление не сможет быть создано из-за ошибок несоответствия типов. Функция REPLACE понадобится для удаления лишних пробелов, возникающих в результате приведения к типу фиксированной длины CHAR:

1 select mixed old,

2 cast(

3 case

4 when

5 replace(

6 translate(mixed,9999999999,0123456789),9, ) =

7 then

8 mixed

9 else replace(

10 translate(mixed,

11 repeat(#,length(mixed)),

12 replace(

13 translate(mixed,9999999999,0123456789),9,)),

14 #,)

15 end as integer ) mixed

16 from V

17 where posstr(translate(mixed,9999999999,0123456789),9) > 0

MySQL

Синтаксис для MySQL немного отличается. Представление V определяется как:

create view V as select concat(

substr(ename,1,2),

replace(cast(deptno as char(4)), ,), substr(ename,3,2) ) as mixed from emp where deptno = 10 union all

select replace(cast(empno as char(4)), ,

from emp where deptno = 20 union all

select ename from emp where deptno = 30

Поскольку MySQL не поддерживает функцию TRANSLATE, придется выполнять обход каждой строки и последовательно обрабатывать все ее символы.

1 select cast(group concat(c order by pos separator ) as unsigned)

2 as MIXED1

3 from (

4 select v.mixed, iter.pos, substr(v.mixed,iter.pos,1) as c

5 from V,

6 ( select id pos from t10 ) iter

7 where iter.pos <= length(v.mixed)