Основные строковые функции и операторы предоставляют разнообразные возможности и возвращают в качестве результата строковое значение. Некоторые строковые функции являются двухэлементными, что означает, что они могут работать одновременно с двумя строками. Стандарт SQL 2003 поддерживает строковые функции.
В SQL 2003 определяется оператор конкатенации (||), который соединяет две отдельные строки в одно строковое значение.
Платформа DB2 поддерживает оператор конкатенации SQL 2003, а также его синоним -функцию CONCAT.
Платформа MySQL поддерживает функцию CONCATQ - синоним оператора конкатенации SQL 2003.
Платформы PostgreSQL и Oracle поддерживают оператор конкатенации SQL 2003 в виде двойной вертикальной черты.
Платформа SQL Server использует знак плюса (+) в качестве синонима оператора конкатенации SQL 2003. В SQL Server есть системный параметр CONCAT_NULL_YIELDS_NULL, который регулирует поведение системы, если при конкатенации строковых значений встречаются значения NULL.
/* Синтаксис SQL 2003 */
stringl || string2 || string3
string1string2string3
/* Для MySQL */
CONCAT("stringl", "string2")
Если любое из соединяемых значений - пустое, то возвращается пустая строка. Кроме того, если в конкатенации принимает участие числовое значение, оно неявно преобразуется в строковое.
SELECT CONCAT("My ", "bologna ", "has ", "a ", "first ", "name…");
My bologna has a first name
SELECT CONCAT("My ", NULL, "has ", "first ", "name…");
CONVERT и TRANSLATE
Функция CONVERT изменяет отображение символьной строки в пределах набора символов и сопоставления. Например, функцию CONVERT можно использовать для изменения числа бит, приходящихся на один символ.
Функция TRANSLATE переводит строковое значение из одного набора символов в другой. Так, функцию TRANSLATE можно использовать для преобразования значения из набора символов English в набор символов Kanji (японский) или Cyrillic (русский). Сам перевод уже должен существовать - либо заданный по умолчанию, либо созданный при помощи команды CREATE TRANSLATION.
CONVERT (символьное_значение USING имя_символьного_преобразования)
TRANSLATE(символьное_значение USING имя_перевода)
Функция CONVERT преобразует символьное значение к набору символов с именем, указанным в параметре имя_символьного преобразования. Функция TRANSLATE преобразует символьное значение к набору символов, указанному в имени_перевода.
Среди рассматриваемых платформ только Oracle поддерживает функции CONVERT и TRANSLATE в том виде, в каком они определяются в стандарте SQL 2003. Реализация функции TRANSLATE в Oracle очень сходна с SQL 2003, но не идентична ему. В этой реализации функция принимает только два аргумента и производит перевод только между набором символов базы данных и набором символов с поддержкой национального языка.
Реализация функции CONV в MySQL только переводит числа с одного основания в другое. А вот в SQL Server реализация функции CONVERT весьма богата возможностями и изменяет тип данных для выражения, но во всех прочих своих аспектах она отличается от функции CONVERТстандарта SQL 2003. Платформа PostgreSQL не поддерживает функцию CONVERT, а реализация функции TRANSLATE преобразует все вхождения символьной строки в любую другую символьную строку.
Платформа DB2 не поддерживает функцию CONVERT, а поддержка функции TRANSLATE не соответствует стандарту ANSI. Функция TRANSLATE используется для преобразования подстрок и, как исторически сложилось, является синонимом функции UPPER, поскольку функция UPPER только недавно была добавлена в DB2. Если функция TRANSLATE используется в DB2 с единственным аргументом в виде символьного выражения, то результатом будет та же строка, преобразованная в верхний регистр. Если функция используется с несколькими аргументами, например TRANSLATE(ucmo4HUK, замена, совпадение), то функция преобразует все символы в источнике, которые также есть в параметре совпадение. Каждый символ в источнике, который находится в том же положении, что в параметре совпадение, будет заменен символом из параметра замена. Ниже приводится пример.
TRANSLATE("Hello, World! ") "HELLO; WORLD!"
TRANSLATE("Hello, World1", "wZ", "1W") "Hewwo, Zorwd1
Платформа MySQL не поддерживает функции TRANSLATE и CONVERT.
Платформа Oracle поддерживает следующий синтаксис функций CONVERT и TRANSLATE.
В реализации Oracle функция CONVERT возвращает текст символьного значения, преобразованный в целевой_набор_символов. Параметр символьное_значение - это строка, которую нужно преобразовать, параметр целевой_набор_символов - это название набора символов, в который нужно преобразовать строку, а параметр исходный _набор символов - это набор символов, в котором строковое значение изначально хранилось.
Функция TRANSLATE в Oracle соответствует синтаксису ANSI, но вы можете выбирать только один из двух наборов символов: набор символов базы данных (CHARJCS) и набор символов с поддержкой национального языка (NCHARJZS).
В Oracle также поддерживается другая функция, которая также называется TRANSLATE (без использования ключевого слова USING). Эта функция TRANSLATE никак не связана с преобразованием наборов символов.
Названия целевого и исходного наборов символов можно передавать либо в виде строковых констант, либо в виде ссылки на столбец таблицы. Обратите внимание, что при преобразовании строки в набор символов, в котором отображаются не все преобразуемые символы, можно подставлять символы-заменители.
Oracle поддерживает несколько общих наборов символов, к которым относятся наборы US7ASCII, WE8DECDEC. WE8HP, F7DEC, WE8EBCDIC500, WE8PC850u WE8ISO8859PI. Например:
SELECT CONVERT("Gro2", "US7ASCII", "WE8HP") FROM DUAL;
Платформа PostgreSQL поддерживает инструкцию CONVERT стандарта ANSI, а преобразования здесь можно определять при помощи команды CREATE CONVERSION. Реализация функции TRANSLATE в PostgreSQL предоставляет расширенный набор функций, которые позволяют преобразовать любой текст в другой текст в пределах указанной строки.
TRANSLATE (символьная строка, из_текста, в_текст)
Вот несколько примеров:
SELECT TRANSLATE("12345abcde", "5а", "XX"); "1234XXbcde" SELECT TRANSLATE(title, "Computer", "PC") FROM titles WHERE type="Personal_computer" SELECT CONVERT("PostgreSQL" USING iso_8859_1_to_utf_8) "PostgreSQL"
Платформа SQL Server не поддерживает функцию TRANSLATE. Реализация функции CONVERT в SQL Server не соответствует стандарту SQL 2003. Эта функция в SQL Server эквивалентна функции CAST.
CONVERT (тип_данных[(длина) | (точность, масштаб)], выражение, стиль])
Предложение стиль используется для определения формата преобразования даты. За дополнительной информацией обращайтесь к документации SQL Server. Ниже приводится пример.
SELECT title, CONVERT(char(7), ytd_sales) FROM titles ORDER BY title GO
Вот полный перечень функций работы со строками, взятый из BOL:
Результат - 11. Чтобы выяснить, какие это буквы, мы можем применить функцию CHAR, которая возвращает символ по известному ASCII-коду (от 0 до 255):
А вот как, например, можно получить таблицу кодов всех алфавитных символов:
| SELECT CHAR(ASCII("a")+ num-1) letter, ASCII("a")+ num - 1 FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x CROSS JOIN (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y CROSS JOIN (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z) x WHERE ASCII("a")+ num -1 BETWEEN ASCII("a") AND ASCII("z") |
Тех, кто еще не в курсе генерации числовой последовательности, отсылаю к соответствующей статье.
Как известно, коды строчных и прописных букв отличаются. Поэтому чтобы получить полный набор без переписывания запроса, достаточно просто дописать к вышеприведенному коду аналогичный:
Я полагаю, что не будет сложным добавить эту букву в таблицу, если потребуется.
Рассмотрим теперь задачу определения нахождения искомой подстроки в строковом выражении. Для этого могут использоваться две функции - CHARINDEX и PATINDEX . Обе они возвращают начальную позицию (позицию первого символа подстроки) подстроки в строке. Функция CHARINDEX имеет синтаксис:
CHARINDEX (искомое_выражение , строковое_выражение [, стартовая_позиция ])
Здесь необязательный целочисленный параметр стартовая_позиция определяет позицию в строковом выражении, начиная с которой выполняется поиск искомого_выражения . Если этот параметр опущен, поиск выполняется от начала строкового_выражения . Например, запрос
Следует отметить, что если искомая подстрока либо строковое выражение есть NULL, то результатом функции тоже будет NULL.
Следующий пример определяет позиции первого и второго вхождения символа "a" в имени корабля "California"
А вот, например, как можно найти имена кораблей, которые содержат последовательность из трех символов, первый и последний из которых есть "e":
Парная к LEFT функция RIGHT возвращает заданное число символов справа из строкового выражения:
RIGHT(<строковое выражения >,<число символов >)
Вот, например, как можно определить имена кораблей, которые начинаются и заканчиваются на одну и ту же букву:
Здесь мы разделяем пробелом имя класса и имя корабля. Кроме того, чтобы не повторять всю конструкцию в качестве аргумента функции, используем подзапрос. Результат будет иметь вид:
Чтобы исключить этот случай, можно воспользоваться еще одной полезной функцией LEN (<строковое выражение >) , которая возвращает число символов в строке. Ограничимся случаем, когда число символов больше единицы:
Функция REPLICATE
дополняет константу "abcde" пятью пробелами справа, которые не учитываются функцией LEN
, - в обоих случаях получаем 5.
Функция DATALENGTH
возвращает число байтов в представлении переменной и демонстрирует нам различие между типами CHAR и VARCHAR. DATALENGTH
даст нам 12 для типа CHAR и 10 - для VARCHAR.
Как и следовало ожидать, DATALENGTH
для переменной типа VARCHAR вернула фактическую длину переменной. Но почему для переменной типа CHAR результат оказался равным 12? Дело в том, что CHAR - это тип фиксированной длины
. Если значение переменной оказывается меньше ее длины, а длину мы объявили как CHAR(12), то значение переменной будет "выровнено" до требуемой длины за счет добавления концевых пробелов.
На сайте имеются задачи, в которых требуется упорядочить (найти максимум и т.д.) в числовом порядке значения, представленные в текстовом формате. Например, номер места в самолете ("2d") или скорость CD ("24x"). Проблема заключается в том, что текст сортируется так (по возрастанию)
Если же требуется упорядочить места в порядке возрастания рядов, то порядок должен быть такой
Если только этим и ограничиться, то получим
Осталось выполнить сортировку
Здравствуйте, уважаемые читатели блога сайт. Сегодня я хотел бы поговорить о языке SQL, а в частности о функциях для обработки текста. Для создания и управления сайтом часто бывает не обязательно знание языка SQL. Системы управления контентом позволяют редактировать контент сайта без написания запросов. Но хотя бы поверхностное знакомство с структурированным языком запросов поможет вам значительно ускорить модификацию и управление данными в базе данных вашего сайта.
Передо мной частенько возникают задачи: удалить часть текста из текстовых полей базы данных, объединить строковые данные или еще что-нибудь связанное с текстом. Делать все это через админские панели сайтов очень неудобно и муторно. Гораздо проще бывает написать запрос к базе данных выполняющий все эти действия за пару секунд.
Итак, начнем...
Начнем по порядку с самого простого. Первой рассмотрим строковую функцию ASCII, которая используется для определения ASCII-кода текстовых символов:
integer ASCII (str string )
Функция возвращает целое значение — ASCII-код первого левого символа строки str. В случае если строка str пустая возвращает 0 и NULL если строка str не существует.
SELECT ASCII ("t");
Результат: 116
SELECT ASCII ("test");
Результат: 116
SELECT ASCII (1);
Результат: 49
integer ORD (str string )
Если первый левый символ строки str многобайтовый, то возвращает его код в формате: ((первый байт ASCII- код)*256+(второй байт ASCII -код))[*256+третий байт ASCII -код...]. В случае если первый левый символ строки str не является многобайтовым, работает как функция ASCII — возвращает его ASCII-код.
SELECT ORD ("test");
Результат: 116
Функция CHAR, тесно связанная с функцией ASCII и выполняет обратное действие:
string CHAR (int integer , ...)
Функция CHAR возвращает строку символов по их ASCII-кодам. Если среди значений встречается значение NULL, то оно пропускается.
SELECT CHAR (116, "101", 115, "116");
Результат: "test"
Одна из самых популярных категорий функций. Ведь частенько бывает нужно объединить значения нескольких полей таблиц базы данных сайта. В языке SQL есть сразу несколько функций для конкатенации строк .
Функция CONCAT:
string CONCAT (str1 string , str2 string ,...)
Функция возвращает строку, созданную путем объединения аргументов. Можно указывать более двух аргументов. Если один из аргументов является NULL, то и возвращаемый результат будет NULL. Числовые значения преобразуются в строку.
SELECT CONCAT ("Hello", " ", "world", "!");
Результат: "Hello world!"
SELECT CONCAT ("Hello", NULL, "world", "!");
Результат: NULL
SELECT CONCAT ("Число пи", "=", 3.14);
Результат: "Число пи=3.14"
Как видно из примеров, строки объединяются без разделителей. Для того чтобы разделить слова в первом примере в качестве аргумента приходится использовать пробел. Если бы слов было больше, то каждый раз вставлять пробелы было бы не очень удобно.
Для таких случаев существует функция CONCAT_WS:
string CONCAT_WS (separator string , str1 string , str2 string ,...)
Функция объединяет строки как и функция CONCAT, но вставляет между аргументами разделитель separator. В случае если аргумент separator является NULL, то и результат будет NULL. Аргументы строки равные NULL пропускаются.
SELECT CONCAT_WS (" ", "Иванов", "Иван", "Иванович");
Результат: "Иванов Иван Иванович"
SELECT CONCAT_WS (NULL, "Иванов", "Иван", "Иванович");
Результат: NULL
SELECT CONCAT_WS (" ", "Иванов", NULL, "Иван", "Иванович");
Результат: ""Иванов Иван Иванович"
В случае объединения большого количества строк, которые необходимо отделять разделителем, функция CONCAT_WS гораздо удобнее функции CONCAT.
Иногда бывает необходимо удлинить строку до определенного количества символов за счет повторения какого-либо символа. Это тоже своего рода объединение строк. Для этого можно использовать функции LPAD и RPAD . Функции имеют следующий синтаксис:
string
LPAD
(str string
, len integer
, padstr string
)
string
RPAD
(str string
, len integer
, padstr string
)
Функция LPAD возвращает строку str дополненную слева строкой padstr до длины len. Функция RPAD выполняет тоже самое, только удлинение происходит с правой стороны.
SELECT LPAD ("test", 10, ".");
Результат: ......test
SELECT RPAD ("test", 10, ".");
Результат: test......
В данных функциях необходимо обратить внимание на параметр len , который ограничивает количество выводимых символов. Поэтому если длина строки str будет больше чем параметр len, то строка будет обрезана:
SELECT LPAD ("test", 3, ".");
Результат: tes
Для определения количества символов в строке в языке SQL отвечает функция LENGTH — длина строки:
integer LENGTH (str string)
Функция возвращает целое число равное количеству символов в строке str.
SELECT LENGTH ("test");
Результат: 4
В случае использования многобайтовых кодировок функция LENGTH выдает не правильный результат. Например в случае если задана кодировка unicode, то запрос:
SELECT LENGTH ("тест");
вернет 8. Что, легко заметить, в два раза больше реального количества символов. В этом случае нужно использовать функцию CHAR_LENGTH:
integer CHAR_LENGTH (str string )
Функция также возвращает длину строки str и поддерживает многобайтовые символы .
Например:
SELECT CHAR_LENGTH ("тест");
Результат: 4
Для вычисления позиции подстроки в строке в языке sql существует несколько функций. Первая, которую мы рассмотрим, функция POSITION:
integer POSITION (substr string IN str string )
Возвращает номер позиции первого вхождения подстроки substr в строке str и возвращает 0 если подстрока не найдена. Функция POSITION может работать с многобайтовыми символами.
SELECT POSITION ("cd" IN "abcdcde");
Результат: 3
SELECT POSITION ("xy" IN "abcdcde");
Результат: 0
Следующая функция LOCATE позволяет начинать поиск подстроки с определенной позиции:
integer LOCATE (substr string, str string , pos integer )
Возвращает позицию первого вхождения подстроки substr в строке str, начиная с позиции pos. Если параметр pos не задан, то поиск осуществляется с начала строки. Если подстрока substr не найдена, то возвращает 0. Поддерживает многобайтовые символы.
SELECT LOCATE ("cd", "abcdcdde", 5);
Результат: 5
SELECT LOCATE ("cd", "abcdcdde");
Результат: 3
Аналогом функций POSITION и LOCATE является функция INSTR:
integer INSTR (str string , substr string )
Также как и функции выше возвращает позицию первого вхождения подстроки substr в строке str. Единственное отличие от функций POSITION и LOCATE то, что аргументы поменяны местами.
Первыми рассмотрим сразу две функции LEFT и RIGHT, которые похожи по своему действию:
string
LEFT
(str string
, len integer
)
string
RIGHT
(str string
, len integer
)
Функция LEFT возвращает len первых символов из строки str, а функция RIGHT столько же последних. Поддерживают многобайтовые символы.
SELECT LEFT ("Москва", 3);
Результат: Мос
SELECT RIGHT ("Москва", 3);
Результат: ква
string
SUBSTRING
(str string
, pos integer
, len integer
)
string
MID
(str string
, pos integer
, len integer
)
Функции позволяют получить подстроку строки str длиною len символов с позиции pos. В случае если параметр len не задан, то возвращается вся подстрока начиная с позиции pos.
SELECT SUBSTRING ("г. Москва — столица России", 4, 6);
Результат: Москва
SELECT SUBSTRING ("г. Москва — столица России", 4);
Результат: Москва — столица России
Примеры с функцией MID не привожу, потому что результаты будут аналогичные.
Интересная функция SUBSTRING_INDEX:
string SUBSTRING_INDEX (str string , delim string , count integer )
Функция возвращает подстроку строки str, полученную путем удаления символов, идущих после разделителя delim, находящимся в позиции count. Параметр count может быть как положительным, так отрицательным. Если count положительный, то отсчет позиции разделителя будет вестись слева и удаляться будут символы находящиеся справа от разделителя. Если count отрицательный, то отсчет позиции разделителя ведется справа и удаляются символы находящиеся слева от разделителя. Возможно, описание получилось слишком запутанным, но на примерах станет понятней.
SELECT SUBSTRING_INDEX ("www.mysql.ru", ".", 1);
Результат: www
В данном примере функция находит, первое вхождения символа точки в строке «www.mysql.ru» и удаляет все символы, идущие после нее, включая сам разделитель.
SELECT SUBSTRING_INDEX ("www.mysql.ru", ".", 2);
Результат: www.mysql
Здесь функция ищет второе вхождение точки, удаляет все символы справа от нее и возвращает получившуюся подстроку. И еще один пример с отрицательным значением параметра count:
SELECT SUBSTRING_INDEX ("www.mysql.ru", ".", -2);
Результат: mysql.ru
В этом примере функция SUBSTRING_INDEX ищет вторую точку, отсчитывая позицию справа, удаляет символы слева от нее и выдает полученную подстроку.
Для удаления лишних пробелов из начала и конца строки в языке SQL есть три функции.
Функция LTRIM:
string LTRIM (str string )
Удаляет с начала строки str пробелы и возвращает результат.
Функция RTRIM:
string RTRIM (str string )
Также удаляет пробелы из строки str, только с конца. Обе функции поддерживают многобайтовые символы.
SELECT LTRIM (" текст ");
Результат: "текст "
SELECT RTRIM (" текст ");
Результат: " текст"
И третья функция TRIM позволяет сразу удалять пробелы из начала и из конца строки:
string TRIM ([ string FROM] str string )
Параметр str обязательный, остальные параметры не обязательные. В случае если задан только один параметр str, то возвращает строку str удалив пробелы из начала и конца строки одновременно.
SELECT TRIM (" текст ");
Результат: "текст"
С помощью пара метра remstr можно задавать символы или подстроки, которые будут удаляться из начала и конца строки. С помощью управляющих параметров BOTH, LEADING, TRAILING можно задавать откуда будут удаляться символы:
SELECT TRIM (BOTH "а" FROM "текст");
Результат: "текст"
SELECT TRIM (LEADING "а" FROM "текстааа");
Результат: "текстааа"
SELECT TRIM (TRAILING "а" FROM "ааатекст");
Результат: "ааатекст"
Функция SPACE позволяет получить строку состоящую из определенного количества пробелов:
string SPACE (n integer )
Возвращает строку, которая состоит из n пробелов.
Функция REPLACE нужна для замены заданных символов в строке :
string REPLACE (str string , from_str string , to_str string )
Функция заменяет в строке str все подстроки from_str на to_str и возвращает результат. Поддерживает многобайтные символы.
SELECT REPLACE ("замена подстроки", "подстроки", "текста")
Результат: "замена текста"
Функция REPEAT:
string REPEAT (str string , count integer )
Функция возвращает строку, которая состоит из count повторений строки str. Поддерживает многобайтовые символы.
SELECT REPEAT ("w", 3);
Результат: "www"
Функция REVERSE переворачивает строку:
string REVERSE (str string )
Переставляет в строке str все символы с последнего на первый и возвращает результат. Поддерживает многобайтовые символы.
SELECT REVERSE ("текст");
Результат: "тскет"
Функция INSERT для вставки подстроки в строку:
string INSERT (str string , pos integer , len integer , newstr string )
Возвращает строку полученную в результате вставки в строку str подстроки newstr с позиции pos. Параметр len указывает сколько символов будет удалено из строки str, начиная с позиции pos. Поддерживает многобайтовые символы.
SELECT INSERT ("text", 2, 5, "MySQL");
Результат: "tMySQL"
"SELECT INSERT ("text", 2, 0, "MySQL");
Результат: "tMySQLext"
SELECT INSERT ("вставка текста", 2, 7, "MySQL");
Результат: "SELECT INSERT ("вставка текста", 2, 7, "MySQL");"
Если вдруг понадобиться заеменить в тексте все заглавные буквы на прописные, то можно воспользоваться одной из двух функций:
string LCASE (str string ) и string LOWER (str string )
Обе функции заменяют в строке str заглавные буквы на прописные и возвращают результат. И та и другая поддерживают многобайтовые символы.
SELCET LOWER ("АБВГДеЖЗиКЛ");
Результат:"абвгдежзикл"
Если же наоборот необходимо прописные буквы заменить заглавными, то также можно применить одну из двух функцийй:
string UCASE (str string ) и string UPPER (str string )
Функции возвращают строку str, заменив все прописные символы на заглавные. Также поддерживают многобайтовые символы.
Пример:
SELECT UPPER ("Абвгдежз");
Результат: "АБВГДЕЖЗ"
Строковых функций в языке SQL немного больше, чем рассмотрено в данной статье. Но так как даже большинство рассмотренных здесь функций используются редко, я закончу их рассмотрение. В следующих статьях я постараюсь рассмотреть реальные практические примеры использования строковых функций SQL. Поэтому не забудьте подписаться на обновления блога . До новых встреч!
В этой части речь пойдет о функциях работы с текстовой информацией, которые могут применяться в запросах и программном коде на языке PL/SQL.
Функция CONCAT(strl, str2)
Данная функция выполняет конкатенацию строк strl и str2. Если один из аргументов равен NULL, то он воспринимается как пустая строка. Если оба аргумента равны NULL, то функция возвращает NULL. Пример:
SELECT CONCAT("У попа \ "была собака") x1,
CONCATCTest"
,
NULL)
х2,
CONCAT(NULL, "Test") х3,
CONCAT(NULL, NULL) x4
FROM
dual
|
У попа была собака |
Для конкатенации строк Oracle поддерживает специальный оператор конкатенации «||», который работает аналогично функции CONCAT, например:
SELECT CONCAT("У попа
\ "была собака")
x1, "У
попа " ||
"была собака" х2
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 INITCAPCИваноВ петр сиДорович")
X
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
Функция REPLACE(str, search_str, [,replace_str])
Функция REPLACE осуществляет поиск образца search_str в строке str и каждое найденное вхождение заменяет на replace_str. По умолчанию replace_str равен пустой строке, поэтому вызов функции REPLACE с двумя аргументами приводит к удалению всех найденных вхождений. Поиск подстроки ведется с учетом регистра. Пример:
SELECT REPLACE("У попа была собака",
"собака", "кошка")
x1,
REPLACE("У попа была злая собака",
"злая")
х2,
REPLACE("У попа была собака",
"Собака", "Кошка") х3
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", "е2\
"Е!") x1,
TRANSLATE("Test 12345", "e234", "E") x2
FROM dual
Данная функция удобна для решения ряда практических задач, связанных с перекодировкой символов или с поиском запрещенных символов. Например, необходимо проанализировать пароль и выяснить, содержит ли он хотя бы одну цифру. Реализация данной проверки при помощи TRANSLATE имеет вид:
IF TRANSLATE(PassWd, "0123456789", "*") = PassWd THEN
ADD_ERR0R("Ошибка
- Пароль должен содержать хотя
бы одну цифру!");
RETURN 1;
END IF;
Другой пример: идет подготовка числа к его преобразованию в NUMBER. Необходимо заменить разделители десятичных знаков «,» и «.» на «.» и удалить пробелы. Реализация данной операции при помощи TRANSLATE имеет вид:
SELECT TRANSLATE("123 455,23", "., ", "
. . ")
x1,
TRANSLATE("-123
455.23", "., ", " . . ") х2
FROM dual
Функция SUBSTR(str, m [,n])
Функция SUBSTR возвращает фрагмент строки str, начиная с символа m длиной n символов. Длину можно не указывать - в этом случае возвращается строка от символа m и до конца строки str. Нумерация символов идет с 1. Если указать m = 0, то копирование все равно начнется с первого символа. Задание отрицательного значения m приводит к тому, что символы отсчитываются от конца строки, а не от начала. Задание значений m, превышающих по абсолютному значению длину строки, приводит к тому, что функция возвращает NULL.
SELECT SUBSTR("У
попа была собака", 13) x1,
SUBSTR("У попа была собака", -6) х2,
SUBSTR("Эto тестовый
текст", 5, 8) х3,
SUBSTR("У попа была собака", 150) х4
FROM dual
|
текстовый |
Функция INSTR(str, search_str [,n[,m]])
Функция INSTR возвращает позицию первого символа m-ro фрагмента строки str, совпадающего со строкой search_str. Сравнение ведется с n-го символа строки str, при сравнении учитывается регистр. По умолчанию n = m = 1, то есть поиск ведется от начала строки и возвращается позиция первого найденного фрагмента. В случае неуспешного поиска функция возвращает 0.
SELECT INSTR("y
попа была собака", "собака") x1,
INSTR("y
попа была собака", "кошка") х2,
INSTR("Это текст для демонстрации поиска текста", "текст",
1, 2) х3,
INSTR(‘11111000000001",
"1", 7) х4
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("") х2,
LENGTH(NULL)
х3,
NVL(LENGTH(""), 0) х4
FROM
dual
Функция LENGTHB аналогична функции LENGTH, но возвращает длину строки в байтах.
Функция ASCII(str)
Возвращает ASCII-код первого символа строки str в случае применения кодировки ASCII и значение первого байта многобайтного символа при использовании кодировки на основе многобайтных символов. Пример:
SELECT ASCII("Test") x1 FROM dual
Функция CHR(n)
Возвращает символ по его коду.
SELECT
CHR(64) x1
FROM dual
В другие. Она имеет следующий синтаксис:
CONV(число,N,M)
Аргумент число находится в системе счисления с основанием N. Функция переводит его в систему счисления с основанием M и возвращает значение в виде строки.
Пример 1
Следующий запрос переводит число 2 из десятичной системы счисления в двоичную:
SELECT CONV(2,10,2);
Результат: 10
Для перевода числа 2E из шестнадцатиричной системы в десятичную требуется запрос:
SELECT CONV("2E",16,10);
Результат: 46
Функция CHAR() переводит ASCII-код в строки. Она имеет следующий синтаксис:
CHAR(n1,n2,n3..)
Пример 2
SELECT CHAR(83,81,76);
Результат: SQL
Следующие функции возвращают длину строки:
Пример 3
SELECT LENGTH("MySQL");
Результат: 5
Иногда бывает полезной функция BIT_LENGTH(строка) , которая возвращает длину строки в битах.
Пример 4
SELECT BIT_LENGTH("MySQL");
Результат: 40
Подстрокой обычно называют часть строки. Часто требуется узнать позицию первого вхождения подстроки в строку. Эту задачу в MySQL решают три функции:
Если подстрока не содержится в строке, то все три функции возвращают значение 0. Функция INSTR() отличается от двух других порядком аргументов. Функция LOCATE() может содержать третий аргумент позиция , который позволяет искать подстроку в строке не с начала, а с указанной позиции.
Пример 5
SELECT LOCATE("Топаз", "открытое акционерное общество Топаз");
Результат: 31
SELECT POSITION("Топаз", "открытое акционерное общество Топаз");
Результат: 31
SELECT INSTR("открытое акционерное общество Топаз",’Топаз’);
Результат: 31
SELECT LOCATE("Топаз", " Завод Топаз и ООО Топаз", 9);
Результат: 20
SELECT LOCATE("Алмаз", "открытое акционерное общество Топаз");
Результат: 0
Функции LEFT(строка, N) и RIGHT(строка, N) возвращают соответственно крайние левые и крайние правые N символов в строке.
Пример 6
SELECT LEFT("СУБД MySQL", 4);
Результат: СУБД
SELECT RIGHT("СУБД MySQL", 5);
Результат: MySQL
Иногда требуется получить подстроку, которая начинается с некоторой заданной позиции. Для этого используются функции:
Обе функции возвращают N символов заданной строки, расположенных начиная с указанной позиции.
Пример 7
SELECT SUBSTRING("СУБД MySQL - одна из самых популярных СУБД", 6,5);
Результат: MySQL
При работе с электронными адресами и адресами сайтов очень полезна функция SUBSTR_INDEX() . Функция имеет три аргумента:
SUBSTR_INDEX(строка, разделитель, N).
Аргумент N может быть положительным или отрицательным. Если он отрицательный, то функция находит N-ое вхождение разделителя, если считать справа. После чего возвращает подстроку, расположенную справа от найденного разделителя. Если N положительно, то функция находит N-ое вхождение разделителя слева и возвращает подстроку, расположенную слева от найденного разделителя.
Пример 8
SELECT SUBSTRING_INDEX("www.mysql.ru",".",2);
Результат: www.mysql
SELECT SUBSTRING_INDEX("www.mysql.ru",".",-2);
Результат: mysql.ru
Функция REPLACE(строка,подстрока1,подстрока2) позволяет заменить в строке все вхождения подстроки1 на подстроку2.
