Строковые функции Sql

Эта группа функций позволяет манипулировать текстом. Строковых функций много, мы рассмотрим наиболее употребительные.

• CONCAT(str1,str2...) Возвращает строку, созданную путем объединения аргументов (аргументы указываются в скобках - str1,str2...). Например, в нашей таблице Поставщики (vendors) есть столбец Город (city) и столбец Адрес (address). Предположим, мы хотим, чтобы в результирующей таблице Адрес и Город указывались в одном столбце, т.е. мы хотим объединить данные из двух столбцов в один. Для этого мы будем использовать строковую функцию CONCAT(), а в качестве аргументов укажем названия объединяемых столбцов - city и address:

  SELECT CONCAT(city, address) FROM vendors;

  
  

  Обратите внимание, объединение произошло без разделения, что не очень читабельно. Давайте подправим наш запрос, чтобы между объединяемыми столбцами был пробел:

  SELECT CONCAT(city, " ", address) FROM vendors;

  
  

  Как видите, пробел считается тоже аргументом и указывается через запятую. Если объединяемых столбцов было бы больше, то указывать каждый раз пробелы было бы нерационально. В этом случае можно было бы использовать строковую функцию CONCAT_WS(разделитель, str1,str2...) , которая помещает разделитель между объединяемыми строками (разделитель указывается, как первый аргумент). Наш запрос тогда будет выглядеть так:

  SELECT CONCAT_WS(" ", city, address) FROM vendors;

  Результат внешне не изменился, но если бы мы объединяли 3 или 4 столбца, то код значительно бы сократился.



• INSERT(str, pos, len, new_str) Возвращает строку str, в которой подстрока, начинающаяся с позиции pos и имеющая длину len символов, заменена подстрокой new_str. Предположим, мы решили в столбце Адрес (address) не отображать первые 3 символа (сокращения ул., пр., и т.д.), тогда мы заменим их на пробелы:

  SELECT INSERT(address, 1, 3, " ") FROM vendors;

  
  

  То есть три символа, начиная с первого, заменены тремя пробелами.



• LPAD(str, len, dop_str) Возвращает строку str, дополненную слева строкой dop_str до длины len. Предположим, мы хотим, чтобы при выводе городов поставщиков они располагались бы справа, а пустое пространство заполнялось бы точками:

  SELECT LPAD(city, 15, ".") FROM vendors;

  
  


• RPAD(str, len, dop_str) Возвращает строку str, дополненную справа строкой dop_str до длины len. Предположим, мы хотим, чтобы при выводе городов поставщиков они располагались бы слева, а пустое пространство заполнялось бы точками:

  SELECT RPAD(city, 15, ".") FROM vendors;

  
  

  Обратите внимание, значение len ограничивает количество выводимых символов, т.е. если название города будет длиннее 15 символов, то оно будет обрезано.



• LTRIM(str) Возвращает строку str, в которой удалены все начальные пробелы. Эта строковая функция удобна для корректного отображения информации в случаях, когда при вводе данных допускаются случайные пробелы:

  SELECT LTRIM(city) FROM vendors;



• RTRIM(str) Возвращает строку str, в которой удалены все конечные пробелы:

  SELECT RTRIM(city) FROM vendors;

  В нашем случае лишних пробелов не было, поэтому и результат внешне мы не увидим.



• TRIM(str) Возвращает строку str, в которой удалены все начальные и конечные пробелы:

  SELECT TRIM(city) FROM vendors;



• LOWER(str) Возвращает строку str, в которой все символы переведены в нижний регистр. С русскими буквами работает некорректно, поэтому лучше не применять. Например, давайте применим эту функцию к столбцу city:

  SELECT city, LOWER(city) FROM vendors;

  
  Видите, какая абракадабра получилась. А вот с латиницей все в порядке:

  SELECT LOWER("CITY");

  
  


• UPPER(str) Возвращает строку str, в которой все символы переведены в верхний регистр. С русскими буквами так же лучше не применять. А вот с латиницей все в порядке:

  SELECT UPPER(email) FROM customers;

  
  


• LENGTH(str) Возвращает длину строки str. Например, давайте узнаем сколько символов в наших адресах поставщиков:

  SELECT address, LENGTH(address) FROM vendors;

  
  


• LEFT(str, len) Возвращает len левых символов строки str. Например, пусть в городах поставщиков выводится только первые три символа:

  SELECT name, LEFT(city, 3) FROM vendors;

  
  


• RIGHT(str, len) Возвращает len правых символов строки str. Например, пусть в городах поставщиков выводится только последние три символа: SELECT LOAD_FILE("C:/proverka");
  Обратите внимание, необходимо указывать абсолютный путь к файлу .

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

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

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

ASCII	NCHAR	SOUNDEX
CHAR	PATINDEX	SPACE
CHARINDEX	REPLACE	STR
DIFFERENCE	QUOTENAME	STUFF
LEFT	REPLICATE	SUBSTRING
LEN	REVERSE	UNICODE
LOWER	RIGHT	UPPER
LTRIM	RTRIM

Начнем с двух взаимно обратных функций - ASCII и CHAR .

Функция ASCII возвращает ASCII-код крайнего левого символа строкового выражения, являющегося аргументом функции.

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

Следует отметить, что аналогичный результат можно получить проще с помощью еще одной функции - LEFT , которая имеет следующий синтаксис:

LEFT (<строковое выражение >, <целочисленное выражение >)

и вырезает заданное вторым аргументом число символов слева из строки, являющейся первым аргументом. Итак,

SELECT DISTINCT LEFT(name, 1) FROM Ships ORDER BY 1

А вот как, например, можно получить таблицу кодов всех алфавитных символов:

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"

SELECT CHARINDEX("a",name) first_a, CHARINDEX("a", name, CHARINDEX("a", name)+1) second_a FROM Ships WHERE name="California"

Обратите внимание, что при определении второго символа в функции используется стартовая позиция, которой является позиция следующего за первой буквой "a" символа - CHARINDEX("a", name)+1. Правильность результата - 2 и 10 - легко проверить:-).

Функция PATINDEX имеет синтаксис:

PATINDEX ("%образец %" , строковое_выражение )

Главное отличие этой функции от CHARINDEX заключается в том, что поисковая строка может содержать подстановочные знаки - % и _. При этом концевые знаки "%" являются обязательными. Например, использование этой функции в первом примере будет иметь вид

Результат выполнения этого запроса выглядит следующим образом:

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

Соединение двух строковых значений в одно называется конкатенацией , и в SQL Server для этой операции используется знак "+" (в стандарте "||"). Итак,

А если строковое выражение будет содержать лишь одну букву? Запрос выведет ее. В этом легко убедиться, написав

Сегодня предлагаю рассмотреть простые примеры использования строковых функций 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. Системы управления контентом позволяют редактировать контент сайта без написания запросов. Но хотя бы поверхностное знакомство с структурированным языком запросов поможет вам значительно ускорить модификацию и управление данными в базе данных вашего сайта.

Передо мной частенько возникают задачи: удалить часть текста из текстовых полей базы данных, объединить строковые данные или еще что-нибудь связанное с текстом. Делать все это через админские панели сайтов очень неудобно и муторно. Гораздо проще бывает написать запрос к базе данных выполняющий все эти действия за пару секунд.

Итак, начнем...

Символьные функции в языке 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 функции для объединения строк

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

Для определения количества символов в строке в языке 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

Для вычисления позиции подстроки в строке в языке 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 можно задавать откуда будут удаляться символы:

• BOTH — удаляет подстроку remstr с начала и с конца строки;
• LEADING — удаляет remstr с начала строки;
• TRAILING — удаляет remstr с конца строки.

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. Поэтому не забудьте подписаться на обновления блога . До новых встреч!