Oracle сортировка строк как чисел

Обновлено: 07.01.2025

который будет работать, я думаю, но как бы я разделил столбец.

различные функции разделения в интернете-это те, которые возвращают таблицу, в то время как в этом случае мне потребуется скалярная функция.

есть ли другие подходы, которые можно использовать? Данные показанный в представлении сетки и представлении сетки не поддерживает сортировку по 2 столбцам по умолчанию (я могу реализовать его, Хотя:)), поэтому, если есть какие-либо более простые подходы, мне было бы очень приятно.

редактировать : спасибо за все ответы. Хотя каждый ответ правильный, я выбрал ответ, который позволил мне включить эти столбцы в сортировку GridView с минимальным повторным факторингом sql-запросов.

рациональное использование REVERSE , CHARINDEX и SUBSTRING , может получить нам то, что мы хотим. Я использовал имена столбцов в моем коде ниже, чтобы проиллюстрировать, что происходит.

настройка выборочных данных:

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

обратите внимание, что все, что вам действительно нужно, это ORDER BY предложение, остальное просто показать мою работу, которая идет следующим образом:

переверните строку, найдите дефис, получите подстроку после дефиса, переверните эту часть: это число без какого-либо аффикса
длина (число без какого-либо аффикса) говорит нам, сколько символов нужно отбросить с самого начала, чтобы получить аффикс, включая дефис. Отбросьте дополнительный символ, чтобы получить только числовую часть, и преобразуйте это в int . К счастью мы получаем перерыв от SQL Server в том, что это преобразование дает ноль для пустой строки.
наконец, получив эти две части, мы просто ORDER BY (число без какого-либо аффикса), а затем (числовое значение аффикса). Это последний приказ, который мы ищем.

код был бы более кратким, если бы SQL Server позволил нам сказать SUBSTRING(value, start) чтобы получить строку, начиная с этого момента, но это не так, поэтому мы должны сказать SUBSTRING(value, start, LEN(value)) много.

Привет, Хабр! В компании, где я работаю, часто проходят (за мат извините) митапы. На одном из них выступал мой коллега с докладом об оконных функциях и группировках Oracle. Эта тема показалась мне стоящей того, чтобы сделать о ней пост.

С самого начала хотелось бы уточнить, что в данном случае Oracle представлен как собирательный язык SQL. Группировки и методы их применения подходят ко всему семейству SQL (который понимается здесь как структурированный язык запросов) и применимы ко всем запросам с поправками на синтаксис каждого языка.

Всю необходимую информацию я постараюсь кратко и доступно объяснить в двух частях. Пост скорее будет полезен начинающим разработчикам. Кому интересно — добро пожаловать под кат.

Часть 1: предложения Order by, Group by, Having

Здесь мы поговорим о сортировке — Order by, группировке — Group by, фильтрации — Having и о плане запроса. Но обо всем по-порядку.

Order by

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

Преимущество Order by в том, что его можно применять и к числовым, и к строковым столбцам. Строковые столбцы обычно сортируются по алфавиту.

Сортировка по возрастанию применяется по умолчанию. Если хотите отсортировать столбцы по убыванию — используйте дополнительный оператор DESC.

SELECT column1, column2, … (указывает на название)
FROM table_name
ORDER BY column1, column2… ASC|DESC;

Давайте все рассмотрим на примерах:

В первой таблице мы получаем все данные и сортируем их по возрастанию по столбцу ID.

Во второй мы также получаем все данные. Сортируем по столбцу ID по убыванию, используя ключевое слово DESC.

В третьей таблице используется несколько полей для сортировки. Сначала идет сортировка по отделу. При равенстве первого оператора для полей с одинаковым отделом применяется второе условие сортировки; в нашем случае — это зарплата.

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

Group by

В SQL оператор Group by собирает данные, полученные из базы данных в определенных группах. Группировка разделяет все данные на логические наборы, что дает возможность выполнять статистические вычисления отдельно в каждой группе.

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

С использованием оператора SQL Group by тесно связано использование агрегатных функций и оператор SQL Having. Агрегатная функция в SQL — это функция, возвращающая какое-либо одно значение по набору значений столбца. Например: COUNT(), MIN(), MAX(), AVG(), SUM()

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Group by стоит после условного оператора WHERE в запросе SELECT. По желанию можно использовать ORDER BY, чтобы отсортировать выходные значения.

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

Решение 1 (без использования группировки):

Решение 2 (с использованием группировки):

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

Второй пример вышел короче и читабельнее, хотя выполняет такие же функции, что и первый.

Как у нас работает Group by: сначала разбивает два отдела на группы qa и dev. Потом для каждого из них ищет максимальную зарплату.

Having

Having это инструмент фильтрации. Он указывает на результат выполнения агрегатных функций. Предложение Having используется в SQL там, где нельзя применить WHERE.

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

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition

Сначала мы выводим отделы со средней зарплатой больше 4000. Затем выводим максимальную зарплату с применением фильтрации.

Решение 1 (без использования GROUP BY и HAVING):

Решение 2 (с использованием GROUP BY и HAVING):

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

План запроса

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

План запроса — это предполагаемый план выполнения запроса, т.е. как СУБД будет его выполнять. СУБД распишет все операции, которые будут выполняться в рамках подзапроса. Проанализировав все, мы сможем понять, где в запросе слабые места и с помощью плана запроса сможем оптимизировать их.

Исполнение любого SQL предложения в Oracle извлекает так называемый “план исполнения”. Этот план исполнения запроса является описанием того, как Oracle будет осуществлять выборку данных, согласно исполняемому SQL предложению. План представляет собой дерево, которое содержит порядок шагов и связь между ними.

К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются: cost — стоимость выполнения и cardinality (или rows) — кардинальность (или количество строк).

Чем больше значение этих показателей, тем менее эффективен запрос.

Ниже можно увидеть анализ плана запроса. В первом решении используется подселект, во втором — группировка. Обратите внимание, что в первом решении обработано 22 строки, во втором — 15.

Анализ плана запроса:

Ещё один анализ плана запроса, в котором применяется два подселекта:

Этот пример приведен как вариант нерационального использования средств SQL и я не рекомендую вам его использовать в своих запросах.

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

Часть 2: Оконные функции

Оконные функции появились ещё в Microsoft SQL Server 2005. Они осуществляют вычисления в заданном диапазоне строк внутри предложения Select. Если говорить кратко, то “окно” — это набор строк, в рамках которого происходит вычисление. “Окно” позволяет уменьшить данные и более качественно их обработать. Такая функция позволяет разбивать весь набор данных на окна.

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

SELECT column_name(s)
Агрегирующая функция (столбец для вычислений)
OVER ([PARTITION BY столбец для группировки]
FROM table_name
[ORDER BY столбец для сортировки]
[ROWS или RANGE выражение для ограничения строк в пределах группы])

OVER PARTITION BY — это свойство для задания размеров окна. Здесь можно указывать дополнительную информацию, давать служебные команды, например добавить номер строки. Синтаксис оконной функции вписывается прямо в выборку столбцов.

Давайте рассмотрим все на примере: в нашу таблицу добавился еще один отдел, теперь в таблице 15 строк. Мы попытаемся вывести работников, их з/п, а также максимальную з/п организации.

В первом поле мы берем имя, во втором — зарплату. Дальше мы применяем оконную функцию over(). Используем её для получения максимальной зарплаты по всей организации, так как не указаны размеры “окна”. Over() с пустыми скобками применяется для всей выборки. Поэтому везде максимальная зарплата — 10 000. Результат действия оконной функции добавляется к каждой строчке.

Если убрать из четвертой строки запроса упоминание оконной функции, т.е. остается только max (salary), то запрос не сработает. Максимальную зарплату просто не удалось бы посчитать. Так как данные обрабатывались бы построчно, и на момент вызова max (salary) было бы только одно число текущей строки, т.е. текущего работника. Вот тут и можно заметить преимущество оконной функции. В момент вызова она работает со всем окном и со всеми доступными данными.

Давайте рассмотрим еще один пример, где нужно вывести максимальную з/п каждого отдела:

Фактически мы задаем рамки для “окна”, разбивая его на отделы. В качестве ранжирующего примера мы указываем department. У нас есть три отдела: dev, qa и sales.

“Окно” находит максимальную зарплату для каждого отдела. В результате выборки мы видим, что оно нашло максимальную зарплату сначала для dev, затем для qa, потом для sales. Как уже упоминалось выше, результат оконной функции записывается в результат выборки каждой строки.

В предыдущем примере в скобках после over не было указано. Здесь мы использовали PARTITION BY, которое позволило задать размеры нашего окна. Здесь можно указывать какую-то доп информацию, передавать служебные команды, например, номер строки.

Заключение

SQL не так прост, как кажется на первый взгляд. Все описанное выше — это базовые возможности оконных функций. С их помощью можно “упростить” наши запросы. Но в них скрыто намного больше потенциала: есть служебные операторы (например ROWS или RANGE), которые можно комбинировать, добавляя больше функциональности запросам.

Расширенные возможности сортировки Oracle выходят далеко за рамки простейшей сортировки A-Z , которая реализуется секцией ORDER BY . Сложности, встречающиеся в международных наборах символов, не решаются простой алфавитной сортировкой. Скажем, в китайском языке существует около 70 000 символов (хотя не все они встречаются при повседневном использовании). Такое разнообразие явно не укладывается в простую схему сортировки.

О порядке сортировки строк часто забывают в ходе глобализации, пока продукт не доберется до группы тестирования. Упорядочение имен работников, городов или клиентов — задача намного более сложная, чем простые формулировки «A предшествует B». Необходимо учесть следующие факторы:

В некоторых европейских символах встречаются диакритические элементы, изменяющие смысл базовой буквы. Скажем, буква «a» отличается от «а». Какая из них должна стоять на первом месте в порядке ORDER BY ?
Каждый локальный контекст может иметь собственные правила сортировки, поэтому многоязыковое приложение должно поддерживать разные правила сортировки в зависимости от текста. Даже регионы с одинаковыми алфавитами могут иметь разные правила сортировки.

Oracle поддерживает три вида сортировки: двоичную, одноязычную и многоязычную. Консорциум Юникода публикует свой алгоритм сортировки, так что мы можем сравнить вывод наших запросов для этих трех типов сортировки с ожидаемыми результатами, приведенными на сайте Юникода.

Двоичная сортировка

Двоичная сортировка основана на кодировке символов. Она работает очень быстро и особенно удобна при работе с данными, которые хранятся в верхнем регистре. Двоичная сортировка чаще всего применяется для ASCII -текста и английского алфавита, но даже в этом случае возможны некоторые нежелательные результаты. Скажем, в ASCII буквы верхнего регистра располагаются до их представлений в нижнем регистре. Следующий пример из схемы g11n демонстрирует результаты двоичной сортировки названий городов в Германии:

Отсортированный список результатов выглядит так:

Обратите внимание на порядок следования городов в списке: Angelholm следует после Zuhlen . Коды символов сортируются по возрастанию; так формируется порядок A-Z в приведенном листинге. Аномалии возникают из-за символов, отсутствующих в английском алфавите.

Одноязычная сортировка

Средства одноязычной сортировки Oracle пригодятся при работе со многими европейскими языками. Вместо базовых кодов в схеме кодировки символов, как при двоичной сортировке, позиция символа при одноязычной сортировке определяется двумя значениями. С каждым символом связывается основное значение, соответствующее базовому символу, и дополнительное значение, определяемое регистром и различиями в диакритических элементах. При несовпадении основных значений порядок сортировки определяется однозначно. Если основные значения совпадают, используется дополнительное значение. Таким образом обеспечивается правильный порядок следования символа «о» по отношению к «о».

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

Получив подтверждение об изменении настроек сеанса, выполним следующий запрос:

Обратите внимание на изменение порядка названий городов:

Гораздо лучше! Порядок следования символов, не входящих в английский алфавит, теперь соответствует правилам немецкого языка. Кстати говоря, если вы не хотите (или не можете) изменять сеансовые настройки NLS , используйте функцию NLSSORT и параметр NLS_SORT в составе запроса:

Функция NLSSORT и параметр NLS_SORT предоставляют простые средства для изменения результатов ORDER BY . Приведенная функция, используемая в последующих примерах, получает параметр NLS_SORT во входных данных. В табл. 1 перечислены некоторые значения параметра NLS_SORT , доступные в Oraclellg .

Таблица 1. Значения параметра NLS_SORT для одноязычной сортировки

arabic	xcatalan	japanese
arabic_abj_sort	german	polish
arabic_match	xgerman	punctuation
arabic_abj_match	german_din	xpunctuation
azerbaijani	xgerman_din	romanian
xazerbaijani	hungarian	russian
bengali	xhungarian	spanish
bulgarian	icelandic	xspanish
canadian french	indonesian	west_european
catalan	italian	xwest_european

Некоторые значения в этом списке начинаются с префикса х: это расширенные режимы сортировки для особых случаев в языке. В приведенном примере с городами некоторые названия содержат символ В. В немецком языке при сортировке этот символ может интерпретироваться как последовательность « ss ». Ранее мы использовали сортировку со значением NLS_SORT = german . Давайте посмотрим, какой результат будет получен в режиме xgerman :

В режиме xgerman слово ABlar переходит с третьего места в списке на четвертое.

Многоязычная сортировка

Как нетрудно догадаться, одноязычная сортировка обладает серьезным недостатком: она работает только с одним языком, заданным параметром NLS_SORT . Oracle также предоставляет многоязычные средства сортировки, позволяющие работать с несколькими локальными контекстами.

Многоязычная сортировка, базирующаяся на стандарте ISO 14651, поддерживает более 1,1 миллиона символов. Oracle поддерживает не только символы, определяемые в стандарте Юникода 4.0, но и некоторые дополнительные символы.

В отличие от двухэтапной одноязычной сортировки, многоязычная сортировка определяет порядок символов в три этапа:

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

Функция NLSSORT и параметр NLS_SORT используются и при многоязычной сортировке, но при этом используются другие значения. Режим GENERIC_M хорошо работает в большинстве западных языков. В табл. 2 перечислены значения параметра NLS_SORT , доступные для многоязычной сортировки.

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

generic_m
canadian_m	japanese_m	schinese_pinyin_m	tchinese_radical_m
danish_m	korean_m	schinese_radical_m	tchinese_stroke_m
french_m	schinese_stroke_m	spanish_m	thai_m

Чтобы продемонстрировать режим многоязычной сортировки, мы изменим вызов функции так, чтобы в нем использовалось значение generic_m :

Символьная функция получает в качестве параметра одно или несколько символьных значений и возвращает символьное и числовое значение. Если символьная функция возвращает символьное значение, оно всегда имеет тип VARCHAR2 (переменная длина) — кроме функций UPPER и LOWER . Эти функции преобразуют заданную строку к верхнему или нижнему регистру соответственно и возвращают значение фиксированной длины типа CHAR , если переданные в аргументах строки имели тип CHAR .

Краткая сводка строковых функций

Как упоминалось ранее, PL/SQL предоставляет в распоряжение программиста широкий набор мощных, высокоуровневых строковых функций для получения информации о строках и модификации их содержимого. Следующий список дает представление об их возможностях и синтаксисе. За полной информацией о конкретных функциях обращайтесь к справочнику Oracle SQL Reference.

ASCII(символ ) Возвращает числовой код (NUMBER) представления заданного символа в наборе символов базы данных.
ASCIISTR(строка1) Получает строку в любом наборе символов и преобразует ее в строку ASCII-символов. Все символы, отсутствующие в кодировке ASCII, представляются в форме \XXXX, где XXXX — код символа в Юникоде.

CHR(код)
Возвращает символ типа VARCHAR2 (длина 1), соответствующий заданному коду. Функция является обратной по отношению к функции ASCII. У нее имеется разновидность, удобная при работе с данными в национальных наборах символов:

Возвращает символ типа NVARCHAR2 из национального набора символов.

COMPOSE(строка1)
Получает строку символов в формате Юникода и возвращает ее в нормализованном виде. Например, ненормализованное представление 'a\0303' определяет символ ' a ' с тильдой cверху (то есть a). Вызов COMPOSE('a\0303') возвращает значение ' \00E3' — шестнадцатеричный код символа a в Юникоде.

В Oracle9i Release 1 функция COMPOSE могла вызываться только из SQL-команд; в программах PL/SQL она использоваться не могла. Начиная с Oracle9i Release2, функция COMPOSE также может использоваться в выражениях PL/SQL.

CONCAT(строка1, строка2)
Присоединяет строку2 в конец строки1. Аналогичного результата можно добиться при помощи выражения строка1 || строка2. Оператор || намного удобнее, поэтому функция CONCAT используется относительно редко.
CONVERT(строка1, набор_символов)
Преобразует строку из набора символов базы данных в заданный набор символов. При вызове также можно задать исходный набор символов:

CONVERT(строка1, конечный_набор, исходный_набор)

Существует две разновидности этой функции:

DECOMPOSE(строка1 CANONICAL)
Выполняет каноническую декомпозицию; полученный результат может быть восстановлен вызовом COMPOSE . Используется по умолчанию.
DECOMPOSE(строка1)
Декомпозиция выполняется в так называемом режиме совместимости. Восстановление вызовом COMPOSE может оказаться невозможным.

Функция DECOMPOSE , как и COMPOSE , не может напрямую вызываться в выражениях PL/SQL в Oracle9i Release 1; ее необходимо вызывать из инструкций SQL. Начиная с Oracle9i Release 2, это ограничение было снято.

Существует несколько разновидностей этой функции:

INSTR(строка1, строка2, начальная_позиция)
Поиск строки2 в строке1 начинается с позиции, заданной последним параметром. По умолчанию поиск начинается с позиции 1, так что вызов INSTR(string1, string2, 1 ) эквивалентен вызову INSTR(string1, string2) .
INSTR(строка1, строка2, отрицательная_начальная_позиция)
Смещение начальной позиции задается не от начала, а от конца строки1.
INSTR(строка1, строка2, начальная_позиция, n )
Находит n-е вхождение строки2, начиная с заданной начальной позиции.
INSTR(строка1, строка2, отрицательная_начальная_позиция, n)
Находит n-е вхождение строки2, начиная с заданной начальной позиции от конца строки1.

Функция INSTR рассматривает строку как последовательность символов. Ее разновидности INSTRB, INSTR2 и INSTR4 рассматривают строку как последовательность байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность INSTRC рассматривает строку как последовательность полных символов Юникода. Например, строка 'a\0303' , которая представляет собой разложенный эквивалент '\00E3', или a , рассматривается как один символ. Напротив, функция INSTR рассматривает 'a\0303 ' как последовательность из двух символов.

LEAST(строка1, строка2, . )
Получает одну или несколько строк и возвращает строку, которая оказалась бы первой (то есть наименьшей) при сортировке входных строк по возрастанию. Также см. описание функции GREATEST , обратной по отношению к LEAST .
LENGTH(строка1)
Возвращает количество символов в строке. Разновидности LENGTHB, LENGTH2 и LENGTH4 возвращают количество байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность LENGTHC возвращает количество полных символов Юникода, нормализованных по мере возможности (то есть с преобразованием 'a\0303 ' в '\00E3' ).

Функция LENGTH обычно не возвращает нуль. Вспомните, что Oracle рассматривает пустую строку ('') как NULL , поэтому вызов LENGTH ('') фактически эквивалентен попытке получить длину NULL ; ее результат тоже будет равен NULL . Единственное исключение встречается при применении LENGTH к типу CLOB . Тип CLOB может содержать 0 байт и при этом быть отличным от NULL . В этом единственном случае LENGTH возвращает 0.

LOWER(строка1)
Преобразует все буквы заданной строки в нижний регистр. Функция является обратной по отношению к UPPER . Тип возвращаемого значения соответствует типу входных данных ( CHAR ,VARCHAR2, CLOB ). Также см. NLS_LOWER .
LPAD(строка1, итоговая_длина)
Возвращает значение строки1, дополненное слева пробелами до итоговой_длины . У функции существует следующая разновидность:
LPAD(строка1, итоговая_длина, заполнитель)
Присоединяет достаточное количество полных или частичных вхождений заполнителя, чтобы общая длина строки достигла заданной итоговой_длины . Например, вызов LPAD ( 'Merry Christmas!', 25, 'Ho! ') вернет результат ' Ho! Ho! HMerry Christmas!'.

?LTRIM(строка1)
Удаляет пробелы с левого края строки1. Также см. описания функций TRIM (стандарт ISO) и RTRIM . У функции существует следующая разновидность:
LTRIM(строка1, удаляемый_набор)
Удаляет любые символы, входящие в строку удаляемый_набор , от левого края строки1.
NCHR(код)
Возвращает символ типа NVARCHAR2 (длина 1) , соответствующий заданному коду. Функция CHR с условием USING NCHAR_CS реализует ту же функциональность, что и NCHR .
NLS_INITCAP (строка1)
Возвращает версию строки1, которая должна относиться к типу NVARCHAR2 или NCHAR , в которой первая буква каждого слова переводится в верхний регистр, а остальные буквы — в нижний. Функция возвращает значение типа VARCHAR2 . «Словом» считается последовательность символов, отделенная от остальных символов пробелом или символом, не являющимся буквенно-цифровым.

Вы можете задать порядок сортировки, влияющий на определение «первой буквы»:

NLS_INITCAP(строка1, 'NLS_SORT=правило_сортировки')
В этой форме синтаксиса правило_сортировки представляет собой одно из допустимых названий правил сортировки, перечисленных в руководстве Oracle Database Globalization Support Guide, Appendix A, раздел «Linguistic Sorts».

Следующий пример показывает, чем функция INITCAP отличается от NLS_INITCAP :

В нидерландском языке последовательность символов « ? » рассматривается как один символ. Функция NLS_INITCAP распознает это обстоятельство при задании правила NLS_SORT и правильно преобразует символы слова « ?zer » («железо» по-нидерландски).

NLS_LOWER(строка1) и NLS_LOWER(строка1, 'NLS_SORT=правило_сортировки ') Возвращает строку1, преобразованную в нижний регистр по правилам заданного языка. О том, как NLS_SORT может повлиять на результат преобразования, рассказано в описании функции NLS_INITCAP .
NLS_UPPER(строка1) и NLS_UPPER(строка1, 'NLS_SORT=правило_сортировки') Возвращает строку1, преобразованную в верхний регистр по правилам заданного языка. О том, как NLS_SORT может повлиять на результат преобразования, рассказано в описании функции NLS_INITCAP .
NLSSORT(строка1) и NLSSORT(строка1, 'NLS_SORT=правило_сортировки ') Возвращает строку байтов, которая может использоваться для сортировки строкового значения по правилам заданного языка. Строка возвращается в формате RAW . Например, сравнение двух строк по правилам французского языка выполняется так: IF NLSSORT(x, 'NLS_SORT=XFRENCH') > NLSSORT(y, 'NLS_SORT=XFRENCH') THEN. Если второй параметр не указан, функция использует порядок сортировки по умолчанию, назначенный для сеанса. Полный список правил приведен в руководстве Oracle Database Globalization Support Guide, Appendix A, раздел «Linguistic Sorts».
REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR За описаниями этих функций, предназначенных для работы с регулярными выражениями, можно изучить эту статью.
REPLACE(строка1, искомая_строка, замена) Возвращает строку, полученную в результате замены всех вхождений искомой_строки в строке1 строкой замена. Функция REPLACE может использоваться для замены всех вхождений определенной подстроки в одной инструкции.
REPLACE(строка1, искомая_строка)
Возвращает строку, полученную в результате удаления всех вхождений искомой_строки из строки1.
RPAD(строка1, итоговая_длина)
Возвращает значение строки1, дополненное справа пробелами до итоговой_длины . У функции существует следующая разновидность:
RPAD(строка1, итоговая_длина, заполнитель)
Присоединяет достаточное количество полных или частичных вхождений заполнителя, чтобы общая длина строки достигла заданной итоговой_длины . Вызов RPAD('Merry Christmas!', 25, 'Ho! ') вернет результат 'Merry Christmas! Ho! Ho!'.

Функция RPAD дополняет строку справа, а парная ей функция LPAD — слева.

RTRIM(строка1)
Удаляет пробелы с правого края строки1. Также см. описания функций TRIM (стандарт ISO) и LTRIM . У функции существует следующая разновидность:
RTRIM(строка1, удаляемый_набор)
Удаляет любые символы, входящие в строку удаляемый_набор , с правого края строки1.
SOUNDEX(строка1)
Возвращает строку с «фонетическим представлением» аргумента.
Пример:

При использовании функции SOUNDEX следует помнить несколько правил:

Значение SOUNDEX всегда начинается с первой буквы входной строки.
Возвращаемое значение генерируется только по первым пяти согласным в строке.
Для вычисления цифровой части SOUNDEX используются только согласные. Все гласные в строке, кроме начальных, игнорируются.
Функция SOUNDEX игнорирует регистр символов; для букв верхнего и нижнего регистра генерируются одинаковые значения SOUNDEX .

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

Алгоритм SOUNDEX ориентирован на английский язык; в других языках он может работать плохо (или не работать вообще).

SUBSTR(строка1, начальная_позиция, длина)
Возвращает подстроку из строки1, которая начинается с начальной_позиции и имеет заданную длину. Если количество символов до конца строки1 окажется меньше длины, возвращаются все символы от начальной позиции до конца строки. У функции существуют следующие разновидности:
SUBSTR(строка1, начальная_позиция)
Возвращает все символы от начальной_позиции до конца строки1.
SUBSTR(строка1, отрицательная_начальная_позиция, длина)
Начальная позиция подстроки отсчитывается от конца строки1.
SUBSTR(строка1, отрицательная_начальная_позиция)
Возвращает последние ABS( отрицательная_начальная_позиция ) строки.

Функция SUBSTR рассматривает строку как последовательность символов. Ее разновидности SUBSTRB, SUBSTR2 и SUBSTR4 рассматривают строку как последовательность байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность SUBSTRC рассматривает строку как последовательность полных символов Юникода. Например, строка 'a\0303' , которая представляет собой разложенный эквивалент '\00E3' , или a , рассматривается как один символ. Напротив, функция SUBSTR рассматривает 'a\0303' как последовательность из двух символов.

TO_CHAR(национальные_символьные_данные)
Преобразует данные из национального набора символов в эквивалентное представление в наборе символов базы данных. Также см. TO_NCHAR .

Функция TO_CHAR также может использоваться для преобразования даты/ времени и чисел в удобочитаемую форму.

TO_MULTI_BYTE(строка1)
Преобразует однобайтовые символы в их многобайтовые эквиваленты. В некоторых многобайтовых кодировках, и прежде всего UTF-8, может существовать несколько вариантов представления одного символа. Скажем, в UTF-8 представление буквы 'G' может содержать от 1 до 4 байт. Для перехода от однобайтового представления к многобайтовому используется функция TO_MULTI_BYTE . Данная функция является обратной по отношению к TO_SINGLE_BYTE .
TO_NCHAR(символы_в_наборе_базы_данных)
Преобразует данные из набора символов базы данных в эквивалентное представление в национальном наборе символов. Также см. TO_CHAR и TRANSLATE. USING.

Функция TO_NCHAR также может использоваться для преобразования даты/времени и чисел в удобочитаемую форму.

TO_SINGLE_BYTE(строка1)
Преобразует многобайтовые символы в их однобайтовые эквиваленты. Функция является обратной по отношению к TO_MULTI_BYTE .
TRANSLATE(строка1, искомый_набор, набор_замены)
Заменяет в строке1 каждое вхождение символа из искомого_набора соответствующим символом набора_замены . Пример:

Если искомый_набор содержит больше символов, чем набор_замены , «лишние» символы, не имеющие соответствия в наборе_замены , не включаются в результат. Пример:

Буква « d » удалена, потому что она присутствует в искомом_наборе , но не имеет эквивалента в наборе_замены . Функция TRANSLATE заменяет отдельные символы, а функция REPLACE — целые строки.

TRANSLATE(текст USING CHAR_CS) и TRANSLATE(текст USING NCHAR_CS)
Преобразует символьные данные в набор символов базы данных ( CHAR_CS ) или в национальный набор символов ( NCHAR_CS ). Выходным типом данных будет VARCHAR2 или NVARCHAR2 в зависимости от того, выполняется ли преобразование к набору символов базы данных или национальному набору символов соответственно.

Функция TRANSLATE. USING входит в число функций SQL по стандарту ISO. Начиная с Oracle9i Release 1, можно просто присвоить значение VARCHAR2 переменной типа NVARCHAR2 , и наоборот — система неявно выполнит нужное преобразование. Если же вы хотите выполнить преобразование явно, используйте функции TO_CHAR и TO_NCHAR для преобразования текста в набор символов базы данных и национальный набор символов соответственно. Oracle рекомендует пользоваться указанными функциями вместо TRANSLATE. USING , потому что они поддерживают более широкий набор входных типов данных.

TRIM(FROM строка1)
Возвращает строку, полученную в результате удаления из строки1 всех начальных и конечных пробелов. У функции существуют следующие разновидности:
TRIM(LEADING FROM . )
Удаление только начальных пробелов.
TRIM(TRAILING FROM . )
Удаление только конечных пробелов.
TRIM(BOTH FROM . )
Удаление как начальных, так и конечных пробелов (используется по умолчанию).
TRIM (. удаляемый_символ FROM строка1)
Удаление вхождений одного удаляемого_символа на выбор программиста.

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

UNISTR(строка1)
Возвращает строку1, преобразованную в Юникод; таким образом, функция является обратной по отношению к ASCIISTR . Для представления непечатаемых символов во входной строке можно использовать запись \XXXX, где XXXX — кодовый индекс символа в Юникоде. Пример:

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

Читайте также: