Oracle добавить символы слева

Обновлено: 15.01.2025

Строки переменной длины. В таких строках указывается максимальная длина строки до 32 767, и заполнение не выполняется.

Строки PL / SQL могут быть переменными или литералами. Строковый литерал заключен в кавычки. Например,

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

Объявление строковых переменных

Если вам нужно объявить строку переменной длины, вы должны указать максимальную длину этой строки. Например, тип данных VARCHAR2. В следующем примере показано объявление и использование некоторых строковых переменных:

Строковые функции и операторы PL / SQL

ASCII , (х);

Возвращает значение ASCII символа x.

CHR , (х);

Возвращает символ со значением ASCII x.

Объединяет строки x и y и возвращает добавленную строку.

Преобразует начальную букву каждого слова в x в верхний регистр и возвращает эту строку.

INSTR (x, find_string [, start] [, вхождение]);

Ищет find_string в x и возвращает позицию, в которой это происходит.

Возвращает расположение строки в другой строке, но возвращает значение в байтах.

Возвращает количество символов в х.

Возвращает длину строки символов в байтах для однобайтового набора символов.

Преобразует буквы в x в нижний регистр и возвращает эту строку.

LPAD (x, width [, pad_string]);

Заполняет x пробелами слева, чтобы довести общую длину строки до ширины символов.

LTRIM (x [, trim_string]);

Обрезает символы слева от x .

NANVL (x, значение);

Возвращает значение, если x соответствует специальному значению NaN (не число), в противном случае возвращается x .

NLS_INITCAP (х);

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

NLS_LOWER (x);

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

NLS_UPPER (х);

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

Изменяет метод сортировки символов. Должен быть указан перед любой функцией NLS; в противном случае будет использоваться сортировка по умолчанию.

NVL (x, значение);

Возвращает значение, если x равно нулю; в противном случае возвращается x.

NVL2 (x, значение1, значение2);

Возвращает значение1, если х не ноль; если x нуль, значение2 возвращается.

UPPER, LOWER

Данные функции уже описывались раньше.

UPPER : приводит строку к верхнему регистру
LOWER : приводит строку к нижнему регистру

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

Конкатенация строк

Конкатенация - это "склейка" строк. Т.е., если у нас есть 2 строки - "Новый", "фотоаппарат", то результатом конкатенации будет строка "Новый фотоаппарат".

Для склейки строк в Oracle используется оператор || .

Поиск подстроки

Для того, чтобы найти вхождение одной строки в другую, используется функция INSTR . Она возвращает позицию вхождения одной строки в другую. Если вхождения не обнаружено, то в качестве результата будет возвращён 0.

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

Как видно, для тех заголовков, которые не содержат восклицательный знак, функция INSTR вернула 0.

В функции INSTR можно задавать позицию, начиная с которой следует производить поиск вхождения:

Данный запрос вернет позицию буквы о в заголовках записей, но поиск будет производить лишь начиная с 3-го символа заголовка.

Так, в строке "Новый фотоаппарат" мы получили результат 8, хотя буква о есть и раньше - на второй позиции.

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

Также можно указать, какое по счету совпадение нужно искать(4-ый параметр в функции INSTR ):

Подобие строк. Like

Для рассмотрения этой темы будем использовать данные из части про сортировку.

Предположим, нам понадобилось посмотреть, какие чаи есть у нас в меню. В данном примере единственный способ, которым мы можем определить, что блюдо является чаем - это проверить, содержится ли слово чай в наименовании.

Но оператор сравнения здесь не подойдет, так как он вернет лишь те строки, которые будут полностью совпадать со строкой Чай.

Перед рассмотрением примера добавим в таблицу меню немного чайных блюд:

Гениальные маркетологи решили, что будут добавлять по одному символу в конце слова чай для обозначения его крепости - "чай%" - совсем слабенький, "чай+" взбодрит с утра, а с "чаем!" можно забыть про сон на ближайшие сутки. Не будем задумываться, почему именно так, а просто примем это как есть.

Итак, первый пример использования LIKE :

Как видно, были получены все блюда, наименования которых начиналось с последовательности символов, составляющей слово Чай. Символ "%" в условии LIKE соответствует любой последовательности символов. Т.е. предыдущий запрос можно было читать так: "Получить все блюда, первые символы наименований которых составляют слово Чай, а после этих символов следует последовательность из любых символов в любом количестве, мне не важно". Кстати, в результат не попал зеленый чай - первые 3 символа наименования у него равны "Зел", но никак не "Чай".

Если не указывать символ "%", то запрос не вернет никаких данных:

При задании шаблонов в LIKE можно использовать следующие символы соответствия:

"%"(знак процента). Ему соответствует 0 или больше символов в значении.
"_"(нижнее подчеркивание). Ему соответствует ровно один символ в значении.

Получим все чаи, названия которых придумали маркетологи(а это любой 1 символ после слова "чай"):

Также, как и при обычном сравнении, учитывается регистр строк. Следующий запрос не вернет никаких данных, т.к. нет блюд, начинающихся со строки "чай", есть только блюда, начинающиеся на "Чай"(первая буква заглавная):

Получим только зеленый чай:

Здесь символ процента был перемещен перед словом "чай", что означает: "Любая последовательность символов(или их отсутствие), заканчивающаяся словом чай".

А для того, чтобы получить список всех блюд, в наименовании которых содержится слово "чай", можно написать следующий запрос:

Выражение ESCAPE в LIKE

Перед рассмотрением выражения опять добавим немного данных в таблицу dishes :

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

Можно выделить некоторый список признаков, по которым мы сможем определить, что кофе с кофеином:

Наименование начинается со слова "Кофе"
Если кофе с кофеином, то в скобках указывается его процентное содержание в виде "n% кофеина", где n - некоторое число.

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

В чем проблема, должно быть понятно - в том, что символ "%" в условии LIKE обозначает совпадение с 0 или больше любых символов.

Для того, чтобы учитывать непосредственно символ "%" в строке, условие LIKE немного видоизменяется:

Здесь после ключевого слова escape мы указываем символ, который будет экранирующим, т.е. если перед символами % будет стоять символ \ , то он будет рассматриваться как совпадение с одним символом % , а не как совпадение 0 и больше любых символов.

Приведение к верхнему регистру. INITCAP

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

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

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

Для замены подстроки в строке используется функция REPLACE . Данная функция принимает 3 параметра, из них последний - не обязательный:

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

Например, получим все "твиты" пользователя johndoe, но в заголовке поста заменим слово "фотоаппарат" заменим на слово "мыльница":

Удаление пробелов. TRIM

Есть 3 основных функции для удаления "лишних" пробелов из строки:

trim - удалить пробелы вначале и в конце строки
ltrim - удалить пробелы вначале строки (слева)
rtrim - удалить пробелы в конце строки (справа)

LPAD, RPAD

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

LPAD (left padding) используется для дополнения строки символами слева, а RPAD (right padding) - для дополнения справа.

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

В предыдущей части статьи мы рассмотрели встроенные арифметические функции. В части 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

У попа была собака

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

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

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

У попа была собака

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

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

Функция LOWER(str)

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

SELECT LOWER('TeXt DATA') X

Функция UPPER(str)

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

SELECT UPPER('TeXt DATA') X

Функция INITCAP(str)

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

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

Иванов Петр Сидорович

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

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

SELECT LTRIM(' TeXt DATA') X1,

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

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

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

SELECT LPAD('Test', 20) x1,

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

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

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

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

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

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

У попа была кошка

У попа была собака

Функция 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

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

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

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

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

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

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

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

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

Оракл для начинающих. Советы программистам, администраторам, IT-специалистам, только начинающим изучать СУБД Oracle. Оракл для чайников.

Строки и символьные функции в Оracle

Строковые литералы в Оracle - это последовательность из нуля, одного или более символов, заключенных в одинарные кавычки.
У новичков часто встречается ошибка, когда строки заключаются в двойные кавычки или наоборот, названия объектов в одинарные.

insert into 'MY_DOC' ('DOC_ID', 'DOC_NAME') values ("1", "Документ 1");

Error at Command Line:1 Column:12
SQL Error: ORA-00903: неверно имя таблицы
00903. 00000 - "invalid table name"

Error at Command Line:1 Column:57
SQL Error: ORA-00984: употребление столбца здесь недопустимо
00984. 00000 - "column not allowed here"

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

Конкатенация (соеднинение) строк выполняется оператором || (две вертикальные черты) или функцией CONCAT

select 'Employee: ' || initcap(ename), concat('Dept: ',deptno)
from emp;

Oracle предлагает обширный набор функций для манипулирования строковыми данными:
CHR (N) - Возвращает символ ASCII кода для десятичного кода N
ASCII (S) - Возвращает десятичный ASCII код первого символа строки
INSTR (S2.S1.pos[,N] - Возвращает позицию строки S1 в строке S2 большую или равную pos.N - число вхождений
LENGHT (S) - Возвращает длину строки
LOWER (S) - Заменяет все символы строки на прописные символы
INITCAP (S) - Устанавливает первый символ каждого слова в строке на заглавный, а остальные символы каждого слова - на прописные
SUBSTR (S,pos,[,len]) - Выделяет в строке S подстроку длиной len, начиная с позиции pos
UPPER (S) - Преобразует прописные букцвы в строке на заглавные буквы
LPAD (S,N[,A]) - Возвращает строку S, дополненную слева симолами A до числа символов N. Символ - наполнитель по умолчанию - пробел
RPAD (S,N[,A]) - Возвращает строку S, дополненную справа симолами A до числа символов N. Символ - наполнитель по умолчанию - пробел
LTRIM (S,[S1]) - Возвращает усеченную слева строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел)
RTRIM (S,[S1]) - Возвращает усеченную справа строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел
TRANSLATE (S,S1,S2) - Возвращает строку S, в которой все вхождения строки S1 замещены строкой S2. Если S1 <> S2, то символы, которым нет соответствия,
исключаются из результирующей строки
REPLACE (S,S1,[,S2]) - Возвращает строку S, для которой все вхождения строки S1 замещены на подстроку S2. Если S2 не указано, то все вхождения подстроки S1
удаляются из результирующей строки
NVL (X,Y) - Если Х есть NULL, то возвращает в Y либо строку, либо число, либо дату в зависимости от исходного типа Y
SOUNDEX (S) - Возвращает фонетическое представление строки
REGEX_INSTR (S,S1,[N1],[N2],[N3],[S2]) - Возвращает позицию в строке S текста, удовлетворяющего регулярному выражению S1
REGEX_REPLACE (S,S1,S2,[N1],[N2],[S3]) - Возвращает строку S, в которой все вхождения строк, удовлетворяющих регулярному выражению S1, замещены на S2
REGEX_SUBSTR (S,S1,[N1],[N2],[S2]) - Возвращает подстроку из строки S, удовлетворяющего регулярному выражению S1

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