Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

Обзор основных функций работы с текстом в SQL ORACLE диалекта и их применения.

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

Мы уже познакомились с оператором LIKE, позволяющим выбирать из базы данных строки с текстом, соответствующие определенному шаблону.

В SQL ORACLE также есть множество полезных сервисных функций для модификации строк, работы с подстроками, объединения строк.

Объединение строк — по-правильному конкатенация.

Для объединения строк в языке SQL диалекта ORACLE используется специальная синтаксическая конструкция ||.

— телевизор.

Рисунок 58. Запрос: пример объединения строк

INstr — поиск позиции подстроки в строке.

INSTR (STR1, STR2, POSn, DIRECTION) — возвращает позицию STR2 в строке STR1,

где поиск осуществляется в позиции POSn

в направлении DIRECTION 1 — от начала строки, 0 — от окончания строки, то есть откуда мы начинаем поиск — от начала строки или с конца строки.

Примеры

Найти первое вхождение буквы «а» в название городов в таблице CITY, вывести на экран и вхождение символа «а».

Рисунок 59. Запрос: пример INSTR

Найти последнее вхождение буквы «а»в название городов в таблице CITY, вывести на экран и номер последнего вхождения символа «а».

Рисунок 60. Запрос к CITY: пример INSTR

Length — длина строки в символах.

LENGTH (str1) возвращает длину строки str1 в символах.

Примеры

Длина строки «AAA»

— 3.

Длина строки «AAA1234»

— 7.

Длина строки «привет мир»

— 10.

Вывести из таблицы MAN имя, фамилию, длину имени и фамилии в символах.

Вывести из таблицы MAN записи *, где длина имени> 5.

Рисунок 62. Запрос к MAN: пример LENGTH

Выбор подстроки из строки SUBSTR

Примеры

Рассмотрим работу данной функции на примерах.

— BCD.

Выбрать все имена из MAN, которые начинаются с «Ан».

Рисунок 63. Пример работы SUBSTR: запрос

Замена подстроки в строке REPLACE

REPLACE (SRCSTR, OLDSUB, NEWSUB) — функция, которая возвращает преобразованную строку SRCSTR, где подстрока OLDSUB из строки SRCSTR заменяется на подстроку NEWSUBю

SRCSTR — оригинальная строка.

OLDSUB — заменяемая подстрока.

NEWSUB — подстрока, на которую заменяем, по умолчанию NULL.

Примеры

Заменить в имени в таблице MAN все буквы а на #.

Рисунок 64. Запрос: пример REPLACE

Заменить в имени в таблице MAN слог «Ма» на #*.

Рисунок 65. Запрос к MAN: пример REPLACE

Еще несколько примеров:

— $$ABB.

— AA, ИНФОAAAA, ИНФОA.

Функцию SUBSTR можно использовать с неполным набором параметров.

Например, в данном случае параметр LEN не задан, поэтому выбираются все символы в строке начиная с Pos.

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

— DEF.

То есть для того чтобы выбрать последние символы в подстроке, достаточно использовать отрицательный параметр pos.

Функция REPLACE поможет убрать ненужные символы из строки.

— млк.

В других СУБД используются такие же функции для работы со строками?

Нет, функции для работы с текстовыми данными и строками несколько различаются в разных СУБД. Но принципиальные различия небольшие. Функции для работы со строками подробно описаны в документации к каждой СУБД.

Можно ли использовать регулярные выражения при выборе данных?

Да, разумеется, и для этого существуют специальные функции. Работу с регулярными выражениями мы рассмотрим далее, в следующих шагах.

1. Найти записи из таблицы МAN, начинающиеся на Ан, использовать SUBSTR.

2. Найти записи из таблицы MAN, где количество символов в фамилии человека больше 10.

3. Вывести из таблицы MANимя, фамилию, количество символов в фамилии, где количество символов в фамилии человека больше 5.

4. Заменить буквы «о» в слове «молоко» на @, вывести результат, использовать REPLACEи DUAL.

5. Подсчитать количество букв «о» в слове «молоко», вывести результат, использовать REPLACE, DUALи математику.

6. Найти первое вхождение буквы «о» в городе с кодом 1 из таблицы CITY.

7. Найти первое и последнее вхождения буквы « и» в городе с кодом 2 из таблицы CITY.

В запросах SQL мы можем использовать результаты математических вычислений.

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

Повторим вычисление математических выражений в SQL.

Для математических выражений используются следующие операции:

А также знакомые нам со школы функции:

Все математические операции выполняются только для числовых значений, числовых колонок с типами NUMBER или производными от NUMBER.

Более подробную информацию можно посмотреть в документации.

Примеры

Вывести из таблицы MAN имя, фамилию и возраст (FIRSTNAME, LASTNAME, YEAROLD) человека, разделенный на 10.

Рисунок 66. Запрос из MAN

Вывести из таблицы MAN имя, фамилию и возраст человека (FIRSTNAME, LASTNAME, YEAROLD), умноженный на sIN (1), округлить до целого.

Рисунок 67. Запрос к таблице MAN с вычислением

Вывести из таблицы CITY записи (*), где квадратный корень от населения города больше 1000 (PEOPLES).

Рисунок 68. Запрос к CITY