Функции и операторы
Обзор функций и операторов
Операторы для символьных типов:
Для числовых типов данных
ABS
SELECT ABS(10), ABS(-10)
ROUND
ROUND - округление дробного числа до ближайшего целого.
SELECT ROUND(2.8), ROUND(3.5189), ROUND(0.001)
SELECT 2.8 as original_num, ROUND(2.8) as round_num UNION ALL SELECT 5.8951475 as original_num, ROUND(5.8951475) as round_num UNION ALL SELECT 2.5 as original_num, ROUND(2.5) as round_num UNION ALL SELECT 2.00001 as original_num, ROUND(2.00001) as round_num
TRUNC
TRUNC - отбрасывает дробную часть.
SELECT 2.8 as original_num, TRUNC(2.8) as trunc_num UNION SELECT 2.001, TRUNC(2.001) UNION SELECT 2.5, TRUNC(2.5) UNION SELECT -2.8, TRUNC(-2.8)
А если указать второй аргумент в запросе, можно отбросить не всю дробную часть, а оставить указанное количество цифр после запятой.
SELECT 2.8411254 as original_num, TRUNC(2.8411254, 2) as trunc_num
FLOOR
FLOOR - возвращает ближайшее целое число, которое меньше аргумента (искомого числа).
SELECT FLOOR (2.8), FLOOR(-2.8)
CEILING
CEILING - возвращает ближайшее целое число, которое больше аргумента (искомого числа).
SELECT CEILING (2.8), CEILING(-2.8)
Примеры работы этих функций
Пример 1: Возьмем album и вычислим среднее значение альбомов на одну музыкальную группу
SELECT n_albums, n_bands, n_albums / n_bands,
cast(n_albums as numeric) / n_bands,
ROUND(cast(n_albums as numeric) / n_bands, 3),
ROUND(cast(n_albums as numeric) / n_bands),
FLOOR(cast(n_albums as numeric) / n_bands),
CEILING(cast(n_albums as numeric) / n_bands)
FROM (
SELECT COUNT(*) as n_albums,
COUNT(DISTINCT band_id) as n_bands
FROM album
) as table_1
Пример 2: Ошибка деления на ноль
SELECT n_albums, n_bands, n_albums / n_bands,
cast(n_albums as numeric) / NULLIF (n_bands, 0)
FROM (
SELECT COUNT(*) as n_albums,
COUNT(DISTINCT band_id) as n_bands
FROM album
) as table_1Когда выполняем деление двух чисел, то возможна ошибка деления на ноль. Чтобы избежать этой ошибки, рекомендуется использовать функцию NULLIF .
Пишется так: NULLIF(n_bands, 0) - в первом параметре колонка, а во втором ноль. Это означает следующее: если колонка n_bands когда-нибудь примет значение 0, то функция NULLIF заменить значение 0 на значение NULL.
LIKE для поиска шаблонов
Оператор LIKE
LIKE сравнивает выражение, в котором содержится текст на совпадение с шаблоном.
В шаблоне знак % - любое количество символов. А знак _ - один символ.
SELECT * FROM album WHERE name LIKE '% Music %'
Экранирование в операторе LIKE
Если нужно найти какую-либо запись, где есть знак %:
В этой ситуации используется символ экранирования. Синтаксис такой:
LIKE '%f%%' ESCAPE 'f'
Первый знак % - спецсимвол, означающий любое количество символов до строки, которую ищем.
f% - поиск процента как отдельного символа в строке, а f - объект экранирования.
Последний знак % - спецсимвол, означающий любое количество символов до строки, которую ищем.
SELECT * FROM band WHERE name LIKE '%f%%' ESCAPE 'f'
По такому же принципу, через экранирование, работает и поиск по знаку _
SELECT * FROM band WHERE name LIKE '_f_%' ESCAPE 'f'
Пример использования LIKE
SELECT * FROM band WHERE name LIKE '%Good%Bad%'
Для символьных типов данных
SUBSTRING
SUBSTRING - поиск подстроки в строке.
SELECT SUBSTRING('This is a book', 11, 4)
-- выводит 4 символа, начиная с 11-ого в строке
SELECT SUBSTRING('This is a book', 22, 4)
-- выводит 3 символа, начиная с 22-ого в строке
POSITION
POSITION - эта функция ищет подстроку в строке, и если она нашлась, возвращает номер символа, начиная с которого, нашлась подстрока.
SELECT POSITION('book' IN 'This is book')
SELECT POSITION('red' IN 'This is book')
CHARACTER_LENGTH
CHARACTER_LENGTH - возвращает количество символов строке
SELECT CHARACTER_LENGTH('This a is book')
TRIM
TRIM - отбрасывает символы слева и справа строки.
SELECT TRIM(' BOOK '),
--если ничего не задавать, убирает пробелы
TRIM('x' FROM 'xxBOOKxx'),
--если задать только символ, который нужно убрать
--убирает его с двух сторон
TRIM(leading 'x' FROM 'xxBOOKxx'),
--leading убирает заданные символы слева
TRIM(trailing 'x' FROM 'xxBOOKxx')
--trailing убирает заданные символы справа
LOWER
LOWER - переводит все символы строки в нижний регистр.
SELECT LOWER('This Is a Book')
UPPER
UPPER - переводит все символы строки в верхний регистр.
SELECT UPPER('This Is a Book')
Примеры использования функций для строк
Пример 1: Надо найти название в таблице, но мы не знаем, в каком регистре она записана.
SELECT * FROM band WHERE lower(name) = 'metallica'
Либо же с использованием функции UPPER
SELECT * FROM band WHERE upper(name) = 'METALLICA'
Пример 2: узнать длину строки в колонке
SELECT name, character_length(name) as len FROM person LIMIT 10
Пример 3: Найдем всех исполнителей, чьи имена или названия групп поставляют более двух слов
SELECT *
FROM person
WHERE POSITION (' ' in name ) > 0
То есть, если POSITION больше нуля, значит где то был найдет пробел в строке.
Так же этот запрос можно записать с помощью LIKE
SELECT * FROM person WHERE name LIKE '% %'
Пример 4: Отделить имя от фамилии музыканта
SELECT name,
character_length(name) as len_name,
--
substring(name, 1, POSITION (' ' in name )-1) as first_name,
character_length(substring(name, 1, POSITION (' ' in name )-1)) as len_first_name,
--
substring(name, POSITION (' ' in name ) +1) as last_name,
character_length(substring(name, POSITION (' ' in name ) +1)) as len_last_name
--
FROM person as table_1
WHERE POSITION (' ' in name ) > 0
Для типов данных даты и времени
TO_DATE
TO_DATE - конвертация из символьного типа в тип даты.
SELECT TO_DATE('2018-12-31', 'yyyy-mm-dd'),
TO_DATE('31-12-2018', 'dd-mm-yyyy')
TO_TIMESTAMP
TO_TIMESTAMP - конвертация из символьного типа в тип даты-времени.
SELECT TO_TIMESTAMP('18.11.1990 05.00.24', 'dd.mm.yyyy hh24.mi.ss'),
TO_TIMESTAMP('1990-11-18 05:00:24', 'yyyy-mm-dd hh24.mi.ss')
И в TO_DATE, и в TO_TIMESTAMP первый аргумент - это текстовое поле, второй - формат даты, записанный в первом аргументе.
TO_CHAR
TO_CHAR - конвертация из типа дата в символьный тип данных.
SELECT TO_CHAR(date '2018-12-31', 'yyyy-mm-dd'),
TO_CHAR(date '1990-11-18', 'dd.mm.yyyy')
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP - выводит текущую дату, текущее время и текущие дату и время соответственно.
SELECT current_date,
current_time,
current_timestamp
EXTRACT
EXTRACT - извлечение отдельных составляющих из типов данных даты и времени
SELECT extract(day FROM date '2018-12-31'), extract(month FROM date '2018-12-31'), extract(year FROM date '2018-12-31')
SELECT current_timestamp,
extract(hour FROM current_timestamp),
extract(minute FROM current_timestamp),
extract(second FROM current_timestamp)