Символьная функция получает в качестве параметра одно или несколько символьных значений и возвращает символьное и числовое значение. Если символьная функция возвращает символьное значение, оно всегда имеет тип VARCHAR2 (переменная длина) — кроме функций UPPER и LOWER. Эти функции преобразуют заданную строку к верхнему или нижнему регистру соответственно и возвращают значение фиксированной длины типа CHAR, если переданные в аргументах строки имели тип CHAR.
Краткая сводка строковых функций
Как упоминалось ранее, PL/SQL предоставляет в распоряжение программиста широкий набор мощных, высокоуровневых строковых функций для получения информации о строках и модификации их содержимого. Следующий список дает представление об их возможностях и синтаксисе. За полной информацией о конкретных функциях обращайтесь к справочнику Oracle SQL Reference.
ASCII(символ) Возвращает числовой код (NUMBER) представления заданного символа в наборе символов базы данных.ASCIISTR(строка1)Получает строку в любом наборе символов и преобразует ее в строку ASCII-символов. Все символы, отсутствующие в кодировке ASCII, представляются в форме \XXXX, где XXXX — код символа в Юникоде.
За информацией о Юникоде и кодах символов обращайтесь на сайт http://unicode.org.
CHR(код)
Возвращает символ типаVARCHAR2(длина 1), соответствующий заданному коду. Функция является обратной по отношению к функции ASCII. У нее имеется разновидность, удобная при работе с данными в национальных наборах символов:
CHR(код USING NCHAR_CS)
Возвращает символ типа 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)
Получает строку в Юникоде и возвращает строку, в которой все составные символы разложены на элементы. Функция является обратной по отношению кCOMPOSE. Например, вызовDECOMPOSE('a') возвращает строку 'a~' (см. описаниеCOMPOSE).
Существует две разновидности этой функции:
DECOMPOSE(строка1 CANONICAL)
Выполняет каноническую декомпозицию; полученный результат может быть восстановлен вызовомCOMPOSE. Используется по умолчанию.DECOMPOSE(строка1)
Декомпозиция выполняется в так называемом режиме совместимости. Восстановление вызовомCOMPOSEможет оказаться невозможным.
Функция DECOMPOSE, как и COMPOSE, не может напрямую вызываться в выражениях PL/SQL в Oracle9i Release 1; ее необходимо вызывать из инструкций SQL. Начиная с Oracle9i Release 2, это ограничение было снято.
GREATEST(строка1, строка2, ...)
Получает одну или несколько строк и возвращает строку, которая оказалась бы последней (то есть наибольшей) при сортировке входных строк по возрастанию. Также см. описание функцииLEAST, обратной по отношению кGREATEST.INITCAP(строка1)
Изменяет регистр символов строкового аргумента, переводя первую букву каждого слова строки в верхний регистр, а остальные буквы — в нижний. Словом считается последовательность символов, отделенная от остальных символов пробелом или символом, не являющимся буквенно-цифровым (например,# или _). Например, вызовINITCAP('this is lower')дает результат'This Is Lower'.INSTR(строка1, строка2)
Возвращает позицию, с которой строка2 входит в строку1; если вхождение не обнаружено, функция возвращает 0.
Существует несколько разновидностей этой функции:
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!'.
У функции LPAD существует парная функция RPAD на примере.
?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:
BEGIN
DBMS_OUTPUT.PUT_LINE(INITCAP('ijzer'));
DBMS_OUTPUT.PUT_LINE(NLS_INITCAP('ijzer','NLS_SORT=XDUTCH'));
END;
Результат:
Ijzer
IJzer В нидерландском языке последовательность символов «?» рассматривается как один символ. Функция 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 ('smith') --> 'S530'
SOUNDEX ('SMYTHE') --> ''S530'
SOUNDEX ('smith smith') --> 'S532'
SOUNDEX ('smith z') --> 'S532'
SOUNDEX ('feuerstein') --> 'F623'
SOUNDEX ('feuerst') --> 'F623' При использовании функции 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 каждое вхождение символа изискомого_наборасоответствующим символомнабора_замены. Пример:
TRANSLATE ('abcd', 'ab', '12') --> '12cd' Если искомый_набор содержит больше символов, чем набор_замены, «лишние» символы, не имеющие соответствия в наборе_замены, не включаются в результат. Пример:
TRANSLATE ('abcdefg', 'abcd', 'zyx') --> 'zyxefg' Буква «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 — кодовый индекс символа в Юникоде. Пример:
BEGIN
DBMS_OUTPUT.PUT_LINE(
UNISTR('Знак евро \20AC')
);
END;
Знак евро €. Функция предоставляет удобный доступ ко всему набору символов Юникода, в том числе и к тем, которые не могут вводиться непосредственно с клавиатуры.
UPPER(строка1)
Преобразует все буквы заданной строки в верхний регистр. Тип возвращаемого значения соответствует типу входных данных (CHAR, VARCHAR2, CLOB). Функция является обратной по отношению кLOWER. Также см.NLS_UPPER.
