Введение в Oracle 10g

Часть 5

Олег Зайцев

Введение

Функция CONCAT(str1, str2)

Функция LOWER(str)

Функция UPPER(str)

Функция INITCAP(str)

Функции LTRIM(str [,set]) и RTRIM(str [,set])

Функции LPAD(str,n, [,char]) и RPAD(str,n, [,char])

Функция REPLACE(str, search_str, [,replace_str])

Функция TRANSLATE(str, from_mask, to_mask)

Функция SUBSTR(str, m [,n])

Функция INSTR(str, search_str [,n[,m]])

Функции LENGTH(str) и LENGTHB(str)

Функция ASCII(str)

Функция CHR(n)

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

Функция CONCAT(str1, str2)

Данная функция выполняет конкатенацию строк str1 и str2. Если один из аргументов равен NULL, то он воспринимается как пустая строка. Если оба аргумента равны NULL, то функция возвращает NULL.

Пример:

SELECT CONCAT('У попа ', 'была собака') x1,

CONCAT('Test', NULL) x2,

CONCAT(NULL, 'Test') x3,

CONCAT(NULL, NULL) x4  

FROM dual

Для конкатенации строк Oracle поддерживает специальный оператор конкатенации «||», который работает аналогично функции CONCAT, например:

SELECT CONCAT('У попа ', 'была собака') x1,

'У попа ' || 'была собака' x2

FROM dual

На заметку

Не следует путать оператор конкатенации «||», эквивалентный вызову функции CONCAT, и оператор «+», применяемый в арифметических операциях. В Oracle это разные операторы, но за счет автоматического приведения типов возможны трудноуловимые ошибки, например:

SELECT '5' + '3' x1

FROM dual

В данном случае возвращается числовое значение 8, а не текстовая строка «53». Это связано с тем, что, обнаружив арифметическую операцию «+», Oracle автоматически пытается привести аргументы к типу NUMBER.

Функция LOWER(str)

Функция LOWER преобразует все символы строки str в строчные.

Пример:

SELECT LOWER('TeXt DATA') X

FROM dual

Функция UPPER(str)

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

Пример:

SELECT UPPER('TeXt DATA') X

FROM dual

Функция INITCAP(str)

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

Пример:

SELECT INITCAP('ИваноВ петр сиДорович') X1      

FROM dual

Функции LTRIM(str [,set]) и RTRIM(str [,set])

Функция LTRIM удаляет все символы с начала строки до первого символа, которого нет в наборе символов set. По умолчанию set состоит из одного пробела и может не указываться. Функция RTRIM аналогична LTRIM, но удаляет символы, начиная от конца строки. Рассмотрим несколько примеров:

SELECT LTRIM('  TeXt DATA') X1,

LTRIM(' _ # TeXt DATA', ' #_') X2,

LTRIM(' 1234567890 TeXt DATA', ' 1234567890') X3  

FROM dual

Функции LPAD(str,n, [,char]) и RPAD(str,n, [,char])

Функция LPAD возвращает строку str, дополненную слева символом char, до достижения строкой длины в n символов. По умолчанию символ-заполнитель равен пробелу и может не указываться. Если длина переданной функции строки больше n, то функция возвращает строку без изменений. Функция RPAD аналогична LPAD, но производит дополнение строки справа. Данные функции очень удобны для форматирования текстовой информации при подготовке отчетов.

Пример:

SELECT LPAD('Test', 20) x1,

RPAD('Test', 20) x2,

LPAD('Test', 20, '_') x3

FROM dual

Функция REPLACE(str, search_str, [,replace_str])

Функция REPLACE осуществляет поиск образца search_str в строке str и каждое найденное вхождение заменяет на replace_str. По умолчанию replace_str равен пустой строке, поэтому вызов функции REPLACE с двумя аргументами приводит к удалению всех найденных вхождений. Поиск подстроки ведется с учетом регистра.

Пример:

SELECT REPLACE('У попа была собака', 'собака', 'кошка') x1,

REPLACE('У попа была злая собака', 'злая') x2,

REPLACE('У попа была собака', 'Собака', 'Кошка') x3

FROM dual

Функция TRANSLATE(str, from_mask, to_mask)

Функция TRANSLATE анализирует строку str и заменяет в ней все символы, встречающиеся в строке from_mask, на соответствующие символы из to_mask. Для корректной работы функции строки from_mask и to_mask должны иметь одинаковую длину или строка from_mask должна быть длиннее, чем to_mask. Если from_mask длинее, чем to_mask, и в процессе обработки строки str обнаружатся символы, соответствующие одному из символов from_mask, и при этом им не найдется соответствия в to_mask, то такие символы будут удалены из строки str. Если передать from_mask или to_mask, равное NULL, то функция возвратит значение NULL. Сравнение производится с учетом регистра.

Примеры:

SELECT TRANSLATE('Test 12345', 'e2', 'E!') x1,

TRANSLATE('Test 12345', 'e234', 'E') x2

FROM dual

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

IF TRANSLATE(PassWd, '0123456789', '*') = PassWd THEN

ADD_ERROR('Ошибка - Пароль должен содержать хотя бы одну цифру !');

RETURN 1;

END IF;

Другой пример: идет подготовка числа к его преобразованию в NUMBER. Необходимо заменить разделители десятичных знаков «,» и «.» на «.» и удалить пробелы. Реализация данной операции при помощи TRANSLATE имеет вид:

SELECT TRANSLATE('123 455,23', '., ', '..') x1,

TRANSLATE('-123 455.23', '., ', '..') x2

FROM dual

Функция SUBSTR(str, m [,n])

Функция SUBSTR возвращает фрагмент строки str, начиная с символа m длиной n символов. Длину можно не указывать — в этом случае возвращается строка от символа m и до конца строки str. Нумерация символов идет с 1. Если указать m = 0, то копирование все равно начнется с первого символа. Задание отрицательного значения m приводит к тому, что символы отсчитываются от конца строки, а не от начала. Задание значений m, превышающих по абсолютному значению длину строки, приводит к тому, что функция возвращает NULL.

Пример:

SELECT SUBSTR('У попа была собака', 13) x1,

SUBSTR('У попа была собака', -6) x2,

SUBSTR('Это тестовый текст', 5, 8) x3,

SUBSTR('У попа была собака', 150) x4

FROM dual

Функция INSTR(str, search_str [,n[,m]])

Функция INSTR возвращает позицию первого символа m-го фрагмента строки str, совпадающего со строкой search_str. Сравнение ведется с n-го символа строки str, при сравнении учитывается регистр. По умолчанию n = m = 1, то есть поиск ведется от начала строки и возвращается позиция первого найденного фрагмента. В случае неуспешного поиска функция возвращает 0.

Примеры:

SELECT INSTR('У попа была собака', 'собака') x1,

INSTR('У попа была собака', 'кошка') x2,

INSTR('Это текст для демонстрации поиска текста', 'текст', 1, 2) x3,

INSTR('11111000000001', '1', 7) x4

FROM dual

На заметку

С данной функций, равно как и со всеми остальными в Oracle, часто допускаются типовые ошибки, связанные с обработкой значения NULL. Если str=NULL, то функция вернет NULL, а не ноль! Это необходимо учитывать при построении различных условий. Например, данный фрагмент программы на PL/SQL как раз не учитывает эту особенность:

IF INSTR(TXT_VAR, '*') = 0 THEN

...

END IF;

В данном случае правильно было бы написать так:

IF NVL(INSTR(TXT_VAR, '*'), 0) = 0 THEN

...

END IF;

Функции LENGTH(str) и LENGTHB(str)

Функция LENGTH(str) возвращает длину строки str в символах. Для пустой строки и значения NULL функция возвращает NULL, поэтому совместно с данной функцией рекомендуется использовать NVL.

Пример:

SELECT LENGTH('У попа была собака') x1,

LENGTH('') x2,

LENGTH(NULL) x3,

NVL(LENGTH(''), 0) x4

FROM dual

Функция LENGTHB аналогична функции LENGTH, но возвращает длину строки в байтах.

Функция ASCII(str)

Возвращает ASCII-код первого символа строки str в случае применения кодировки ASCII и значение первого байта многобайтного символа при использовании кодировки на основе многобайтных символов.

Пример:

SELECT ASCII('Test') x1

FROM dual

Функция CHR(n)

Возвращает символ по его коду.

Пример:

SELECT CHR(64) x1

FROM dual

Заключение

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

КомпьютерПресс 9'2006