Функции преобразования значений

Практическая работа 10

Преобразование вывода и встроенные функции

В SQL реализованы операторы преобразования данных и встроенные функции, предназначенные для работы со значениями столбцов и/или константами в выражениях. Использование этих операторов допустимо в запросах везде, где допустимы выражения.

Числовые, символьные и строковые константы

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

Если в запросе вместо спецификации столбца SQL обнаруживает число, то оно интерпретируется как числовая константа.

Символьные константы должны указываться в одинарных кавычках. Если одинарная кавычка должна выводиться как часть строковой константы, то ее нужно предварить другой одинарной кавычкой.

Например, результатом выполнения запроса

SELECT /Фамилия', SURNAME, 'Имя', NAME, 100

FROM STUDENT;            

является таблица следующего вида:

Арифметические операции для преобразования числовых данных

• Унарный (одиночный) оператор «—» (знак минус) изменяет знак числового значения, перед которым он указан, на противоположный.

 • Бинарные операторы «+», «—», «*» и «/» предоставляют возможность выполнения арифметических операций сложения, вычитания, умножения и деления.

Например, результат запроса

SELECT SURNAME, NAME, STIPEND, -(STIPEND*KURS)/2
               FROM STUDENT
               WHERE KURS = 4 AND STIPEND > 0;

выглядит следующим образом:

Операция конкатенации строк

Операция конкатенации «||» позволяет соединять («склеивать») значения двух или более столбцов символьного типа или символьных констант в одну строку.

Эта операция имеет синтаксис

<значимое символьное выражение> {||} <значимое символьное выражение >.

Например:

SELECT SURNAME || '_' || NAME, STIPEND
                 FROM STUDENT
                WHERE KURS = 4 AND STIPEND > 0;

Результат запроса будет выглядеть следующим образом:

Функции преобразования символов в строке

• LOWER — перевод в строчные символы (нижний регистр)

lower (<строка>)

• UPPER — перевод в прописные символы (верхний регистр)

upper (< строка >)

• initcap — перевод первой буквы каждого слова строки в прописную (заглавную)

initcap (<строка>

Например:

SELECT LOWER (S U RNAME), UPPER (NAME)
               FROM STUDENT
                WHERE KURS = 4 AND STIPEND > 0;

Результат запроса будет выглядеть следующим образом:

Строковые функции

• lpad — дополнение строки слева

lpad (<строка>,<длина>[,<подстрока>])

• <строка> дополняется слева заданной в <подстроке> последовательностью символов до указанной <длины> (возможно, с повторением последовательности);

• если <подстрока> не указана, то по умолчанию <строка> дополняется пробелами;

• если <длина> меньше 'длины <строки>, то исходная <строка> усекается слева до заданной <длины>.

•  rpad — дополнение строки справа

rpad (<строка>,<длина>[,<подстрока>])

 • <строка> дополняется справа заданной в <подстроке> последовательностью символов до указанной <длины> (возможно, с повторением последовательности);

• если <подстрока> не указана, то по умолчанию <строка>дополняется пробелами;

• если <длина> меньше длины <строки>, то исходная <строка> усекается справа до заданной <длины >.

•  ltrim — удаление левых граничных символов

LTRIM (<строка>[,<подстрока>])

• из <строки> удаляются слева символы, указанные в <подстроке>;

• если <подстрока> не указана, по умолчанию удаляются пробелы;

• в <строку> справа добавляется столько пробелов, сколько символов слева было удалено, то есть длина <строки> остается неизменной.

• RTRIM — удаление правых граничных символов

rtrim (<строка>[,<подстрока>])

• из <строки> удаляются справа символы, указанные в <подстроке>;

• если <подстрока> не указана, по умолчанию удаляются пробелы;

• в <строку> слева добавляется столько пробелов, сколько символов справа было удалено, то есть длина <строки> остается неизменной.

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

Например, константы ' ААА' и 'ААА ' не равны друг другу.

• SUBSTR — выделение подстроки

SUBSTR (<строка>,<начало>[,<количество>])

• из <строки> выбирается заданное <количество> символов, начиная с указанной параметром <начало> позиции в строке;

• если <количество> не задано, символы выбираются с <начала> и до конца < строки >;

• возвращается подстрока, содержащая число символов, заданное параметром <количество > либо число символов от позиции, заданной параметром <начало> до конца строки;

• если указанное <начало> превосходит длину <строки>, то возвращается строка, состоящая из пробелов. Длина этой строки будет равна заданному <количеству> или исходной
длине <строки> (при не заданном <количестве>).

• INSTR — ПОИСК ПОДСТРОКИ

INSTR (<строка>,<подстрока>[,<начало поиска>[,<номер вхождения>]])

• <начало поиска> задает начальную позицию в строке для поиска <подстроки>. Если не задано, то по умолчанию принимается значение 1;

• <номер вхождения> задает порядковый номер искомой подстроки. Если не задан, то по умолчанию принимается значение 1;

• значимые выражения в <начале поиска> или в <номере вхождения> должны иметь беззнаковый целый тип или приводиться к этому типу;

• тип возвращаемого значения — INT;

функция возвращает позицию найденной подстроки.

• length — определение длины строки

LENGTH(<стpокa>)

• длина <строки>, тип возвращаемого значения — INT;

• функция возвращает NULL, если <строка> имеет NULL-значение.

Примеры запросов, использующих строковые функции

Результат запроса

SELECT LPAD (SURNAME, 10, '@'), RPAD (NAME, 10, '$')

FROM STUDENT

WHERE KURS - 3 AND STIPEND > 0;

будет выглядеть следующим образом:

А запрос

SELECT SUBSER(NAME, 1, 1)   || '. * || SURNAME, CITY, LENGTH (CITY;

FROM STUDENT

WHERE KURS IN(2, 3, 4)AND STIPEND > 0;

выдаст результат:

Функции работы с числами

• ABS— абсолютное значение

ABS(<значимое числовое выражение>)

• FLOOR — урезает значение числа с плавающей точкой до наи­большего целого, не превосходящего заданное число

FLOOR(<значимое числовое выражение>)

• ceil— самое малое целое, равное или большее заданного числа CEIL (<значимое числовое выражение>).

• Функция округления — ROUND

round(<значимое числовое выражение>,<точность>)

аргумент <точность> задает точность округления (см. примерниже)

• Функция усечения — TRUNC

trunc(оначимое числовое выражение>,<точность>)

• Тригонометрические функции — COS, SIN, tancos (<значимое числовое выражение>)

SIN(<значимое числовое выражение>) tan(<значимое числовое выражение>)

• Гиперболические функции — cosh, sinh, tanhcosh (<значимое числовое выражение>)

SINH(<значимое числовое выражение>) tanh(<значимое числовое выражение>)

• Экспоненциальная функция — ехрехр (<значимое числовое выражение>)

• Логарифмические функции — LN, LOGLN (<значимое числовое выражение>) log (<значимое числовое выражение>)

• Функция возведения в степень — POWER

power(<значимое числовое выражение>,<экспонента>)

• Определение знака числа — SIGN SIGN (<значимое числовое выражение>)

• Вычисление квадратного корня — SQRTSQRT (<значимое числовое выражение>)

Пример

Запрос

SELECT UNIV_NAME, RATING, ROCJND(RATING, -1) , ТРОЯС (RATING, -1)

FROM UNIVERSITY;                   '

вернет результат:

Функции преобразования значений

• Преобразование в символьную строку — TO_CHAR TO_char(<значимое выражение>[,<символьный формат>])

• <значимое выражение > — числовое значение или значение типа дата-время;

• для числовых значений <символьный формат> должен иметь синтаксис [S]9[9][.9[9]], где S — представление зна­ка числа (при отсутствии предполагается без отображения знака), 9 — представление цифр-знаков числового значе­ния (для каждого знакоместа). Символьный формат опре­деляет вид отображения чисел. По умолчанию для число­вых значений используется формат '999999.99';

• для значений типа дата-время <символьный формат> имеет вид (то есть вид отображения значений даты и вре­мени):

— в части даты

‘DD-Mon-YY’

‘DD-Mon-YYYY’

‘MM/DD/YY’

‘MM/DD/YYYY’

‘DD.MM.YY’

DD.MM.YYYY’

— в части времени

'НН24' 'НН24:МГ 'HH24:MI:SS' ^<HH24:MI:SS.FF

где: НН24 — часы в диапазоне от 0 до 24 Ml — минуты SS — секунды FF — тики (сотые доли секунды)

При выводе времени в качестве разделителя по умолчанию используется двоеточие (:), но при желании можно использо­вать любой другой символ.

Возвращаемое значение — символьное представление Зна­чимого выражения> в соответствии с заданным <символьным форматом> преобразования.

• Преобразование из символьного значения в числовое —

TOJTOMBER

то number (<значимое символьное выражение>) При этом <значимое символьное выражение > должно зада­вать символьное значение числового типа.

• Преобразование символьной строки в дату — to_date to_date (<значимое символьное выражение>[,<символьный формат>])

• <значимое символьное выражение> должно задавать сим­вольное значение типа дата-время;

• <символьный формат> должен описывать представление значения типа дата-время в <значимом символьном выражении>. Допустимые форматы (в том числе и формат по умолчанию) приведены выше.

Возвращаемое значение — <значимое символьное выражение> во внутреннем представлении. Тип возвращаемого значе­ния — date.

Над значениями типа date разрешены следующие операции:

• бинарная операция сложения;

• бинарная операция вычитания.

В бинарных операциях один из операндов должен иметь значение отдельного элемента даты: только год, или только месяц, илитолько день. Например:

 придобавлении к дате '22.05.1998' пяти лет получится дата '22.05.2003';

• при добавлении к этой же дате девяти месяцев получится да- та '22.02.1998';

• придобавлении 10 дней получим '01.06.1998'.

Присложении двух полных дат, например, '22.05.1998' + ‘01.12.2000',результат непредсказуем.

Пример

Запрос