В этой части речь пойдет о функциях работы с текстовой информаци­ей, которые могут применяться в запросах и программном коде на языке 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

Последнее обновление: 29.07.2017

Для работы со строками в T-SQL можно применять следующие функции:

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

SELECT LEN("Apple") -- 5

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

SELECT LTRIM(" Apple")

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

SELECT RTRIM(" Apple ")

CHARINDEX : возвращает индекс, по которому находится первое вхождение подстроки в строке. В качестве первого параметра передается подстрока, а в качестве второго - строка, в которой надо вести поиск:

SELECT CHARINDEX("pl", "Apple") -- 3

PATINDEX : возвращает индекс, по которому находится первое вхождение определенного шаблона в строке:

SELECT PATINDEX("%p_e%", "Apple") -- 3

LEFT : вырезает с начала строки определенное количество символов. Первый параметр функции - строка, а второй - количество символов, которые надо вырезать сначала строки:

SELECT LEFT("Apple", 3) -- App

RIGHT : вырезает с конца строки определенное количество символов. Первый параметр функции - строка, а второй - количество символов, которые надо вырезать сначала строки:

SELECT RIGHT("Apple", 3) -- ple

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

SELECT SUBSTRING("Galaxy S8 Plus", 8, 2) -- S8

REPLACE : заменяет одну подстроку другой в рамках строки. Первый параметр функции - строка, второй - подстрока, которую надо заменить, а третий - подстрока, на которую надо заменить:

SELECT REPLACE("Galaxy S8 Plus", "S8 Plus", "Note 8") -- Galaxy Note 8

REVERSE : переворачивает строку наоборот:

SELECT REVERSE("123456789") -- 987654321

CONCAT : объединяет две строки в одну. В качестве параметра принимает от 2-х и более строк, которые надо соединить:

SELECT CONCAT("Tom", " ", "Smith") -- Tom Smith

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

SELECT LOWER("Apple") -- apple

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

SELECT UPPER("Apple") -- APPLE

SPACE : возвращает строку, которая содержит определенное количество пробелов

Например, возьмем таблицу:

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

И при извлечении данных применим строковые функции:

SELECT UPPER(LEFT(Manufacturer,2)) AS Abbreviation, CONCAT(ProductName, " - ", Manufacturer) AS FullProdName FROM Products ORDER BY Abbreviation

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

Мы с Вами уже рассматривали строковые функции SQL , но так как реализации данного языка в разных СУБД различны, например некоторых функций нет в Transact-SQL, а в PL/PGSql они есть, и как раз в прошлый раз мы рассматривали строковые функции, которые можно использовать в plpgsql и поэтому сегодня мы поговорим именно о Transact-SQL.

Как можно сочетать SUBSTRING, CHARINDEX и LEN

И так, к примеру, Вам необходимо искать в строке ее часть по определенному критерию и вырезать ее, и не просто искать однотипную часть, а динамически, т.е. для каждой строки строка поиска будет разная. Примеры будем писать в Management Studio SQL Server 2008.

Для этого мы будем использовать следующие функции:

• SUBSTRING (str, start, len) – данная функция вырезает часть строки из другой строки. Имеет три параметра 1. Это сама строка; 2. Начальная позиция, с какой необходимо начать вырезать; 3. Количество символов, сколько необходимо вырезать.
• CHARINDEX (str1, str2)- ищет str1 в str2 и возвращает порядковый номер первого символа в случае нахождения такой строки. Имеет третий не обязательный параметр, с помощью которого можно указать с какой стороны начинать поиск.
• LEN (str1)-длина строки, т.е. количество символов.

Как Вы видите, здесь я использовал объявление переменных, а Вы вместо переменных можете подставлять свои поля в запросе. Вот сам код:

Declare @rezult as varchar(10) --исходная строка declare @str1 as varchar(100) --строка поиска declare @str2 as varchar(10) set @str1 = "Пробная строка string для поиска в ней другой строки" set @str2 = "string" set @rezult=substring(@str1,CHARINDEX(@str2, @str1), LEN(@str2)) select @rezult

Смысл здесь вот в чем, мы, используя функцию len узнаем, сколько символов необходимо вырезать, а charindex задает ту позицию, с которой необходимо начинать вырезать, и соответственно substring выполняет саму выборку в строке.

Как можно сочетать LEFT, RIGHT и LEN

Допустим, что Вам необходимо получить первые несколько символов в строке или проверить эти первые символы в строке на наличие чего-либо, например, какой-то номер, а его длина естественно разная (пример естественно тестовый).

• Left (str, kol) – функции вырезает указанное количество символов с лева, имеет два параметра первой это строка а второй соответственно количество символов;
• Right (str, kol) — функции вырезает указанное количество символов с право, параметры те же самые.

Теперь будем использовать простые запросы к таблице

Для начала давайте создадим таблицу test_table:

CREATE TABLE ( IDENTITY(1,1) NOT NULL, (18, 0) NULL, (50) NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON GO

Заполним ее тестовыми данными и напишем вот такие запросы:

Как Вы понимаете первый запрос это просто выборка всех строк (Основы SQL — оператор select), а второй это уже непосредственно сочетание наших функций, вот код:

Select * from test_table select number, left(text,LEN(number)) as str from test_table

А если бы эти номера были справа, то мы бы использовали функцию RIGHT .

Использование Rtrim, Ltrim, Upper и Lower в сочетании

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

• Rtrim (str) –удаляет пробелы справа;
• Ltrim (str) – удаляет пробелы слева;
• Upper (str) – приводит строку в верхний регистр;
• Lower (str) — приводит строку в нижний регистр.

Как видите, для закрепления мы здесь использовали еще и Substring и Len . Смысл запроса прост, мы удаляем пробелы и справа и слева, затем приводим первый символ к верхнему регистру путем вырезания его, далее мы конкатенируем (оператор +) этот символ с оставшейся строкой. Вот код:

Declare @str1 as varchar(100) set @str1 = " тестовая строка с пробелами в начале и в конце " select @str1 select upper(substring(rtrim(ltrim(@str1)),1,1))+ lower(substring(rtrim(ltrim(@str1)),2,LEN(rtrim(ltrim(@str1)))-1))

На сегодня я думаю достаточно, и если Вам нравиться программировать на SQL то на этом сайте мы не раз затрагивали эту очень интересную тему, например.

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

SELECT name || surname AS FIO FROM table

Или чтобы отделить пробелом введите

SELECT name || " " || surname AS FIO FROM table

т.е. две вертикальные черты объединяют два столбца в один, а чтобы отделить их пробелом я поставил между ними пробел (можно использовать любой символ, например тире или двоеточие ) в апострофах и объединил также двумя вертикальными чертами (в Transact-SQL вместо двух вертикальных черточек используется знак + ).

Функция INITCAP

Дальше идет также очень полезная функция, INITCAP – которая возвращает значение в строке, в которой каждое слово начинается с заглавной буквы, а продолжается маленькими. Это нужно для того, если у Вас в той или иной колонке не соблюдают правила заполнения и для того чтобы вывести все это дело в красивом виде можно использовать данную функцию, например, у Вас в таблице записи в колонке name следующего вида: ИВАН иванов или петр петров, Вы применяете данную функцию.

SELECT INITCAP (name) AS FIO FROM table

И у Вас получится вот так.

Функция UPPER

Похожая функция, только возвращает все символы с заглавной буквы, это UPPER .

SELECT UPPER (name) AS FIO FROM table

• name – название колонки;
• 20 – количество знаков (длина поля );
• ‘-‘ – символ, которым нужно дополнить до необходимого количества знаков.

Функция RPAD

Сразу рассмотрим обратную функцию. RPAD – действие и синтаксис тот же что и у LPAD, только дополняются символы справа (в LPAD слева ).

SELECT RPAD (name, 20, "-") AS name FROM table

Иван—————-

Сергей—————

Функция LTRIM

Далее идет тоже в некоторых случаях полезная функция, LTRIM – эта функция удаляет крайние левые символы, которые Вы укажите. Например, у Вас в базе есть колонка «город», в которой город указан в виде «г.Москва», а также есть города которые указанны в виде просто «Москва». Но Вам нужно вывести отчет только в виде «Москва» без «г.», но как это сделать, если есть и такие и такие? Вы просто указываете своего рода шаблон «г.» и если крайние левые символы начинаются с «г.», то эти символы просто не будут выводиться.

SELECT LTRIM (city, "г.") AS gorod FROM table

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

Функция RTRIM

Также давайте сразу рассмотрим обратную функцию. RTRIM – то же самое что и LTRIM только символы ищутся справа.

Примечание! В Transact-SQL функции RTRIM и LTRIM удаляют пробелы справа и слева соответственно.

Функция REPLACE

Теперь рассмотрим такую интересную функцию как REPLACE – она возвращает строку, в которой все совпадения символов, заменяются на Ваши символы, которые Вы укажите. Для чего ее можно использовать, например, у Вас в базе есть колонки, в которых встречаются некие разделительные символы, допустим «/». Например, Иван/Иванов, а Вам хотелось бы вывести Иван-Иванов, то напишите

SELECT REPLACE (name, "/", "-") FROM table

и у Вас произойдет замена символов.

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

Функция TRANSLATE

TRANSLATE – строковая функция, которая заменяет все символы в строке, на те символы, которые Вы укажите. Исходя из названия функции, можно догадаться, что это полный перевод строки. Отличие данной функции от REPLACE в том, что она заменяет каждый символ, который Вы укажите, т.е. у Вас есть три символа, допустим абв и с помощью TRANSLATE Вы его можете заменить на abc таким образом у Вас а=a, б=b, в=c и по такому принципу будут заменяться все совпадения символов. А если Вы заменяли с помощью REPLACE, то у Вас искалось только полное совпадение символов абв расположенных подряд.

Функция SUBSTR

SUBSTR – данная функция, возвращает только тот диапазон символов, который Вы укажите. Другими словами, допустим, строка из 10 символов, а Вам все десять не нужны, а допустим, нужны только 3-8 (с третьего по восьмой ). С помощью данной функции Вы легко можете это сделать. Например, у Вас в базе есть какой-нибудь идентификатор, фиксированной длинны (типа: AA-BB-55-66-CC) и каждая комбинация символов что-то означает. И в один прекрасный момент Вам сказали вывести только 2 и 3 комбинацию символов, для этого вы пишите запрос следующего вида.

SELECT SUBSTR (ident, "4", "8") FROM table

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

Функция LENGTH – длина строки

Следующая функция также может пригодиться, это LENGTH – которая просто на всего считает количество символов в строке. Например, Вам нужно узнать, сколько символов в каждой ячейки столбца допустим «name», таблица следующего вида.

SELECT LENGTH (name) FROM table

после этого запроса Вы получите вот это.

4

6

7

Вот мы с Вами и рассмотрели основные строковые функции SQL. В следующих статьях мы продолжим изучение SQL.

Вот полный перечень функций работы со строками, взятый из 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"). Проблема заключается в том, что текст сортируется так (по возрастанию)

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

Если только этим и ограничиться, то получим

Осталось выполнить сортировку