Описание и примеры строковых функций

Текстовый материал

Многие алгоритмы, которые чаще всего требуются в повседневной работе, разработчики Microsoft SQL Server уже реализовали в виде системных функций.

Например, Вам уже известны агрегатные функции, которые мы затрагивали на предыдущем уровне курса. К ним относятся COUNT, SUM, MAX, MIN, AVG, с помощью них мы можем получить, например, сумму значений в столбце или количество записей.

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

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

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

Строковые функции – это функции, которые работают со строками, иными словами, с текстовыми значениями в столбце, т.е. выполняют различные операции над текстом.

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

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

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

LTRIM, RTRIM и TRIM

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

Функция LTRIM убирает все пробелы в начале строки, функция RTRIM в конце.

Во многие системные функции необходимо передавать параметры. В функции LTRIM и RTRIM нужно передать саму текстовую строку, из которой нужно удалить пробелы, это может быть как просто текст, так и столбец, т.е. название столбца в таблице.

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

Для наглядности столбец ProductName мы выведем несколько раз, чтобы видеть, как изменяется результирующий набор данных. Также здесь показано, что функции можно сочетать, т.е. вкладывать друг в друга, например, в данном запросе мы к столбцу ProductName сначала применили функцию LTRIM, а затем RTRIM (колонка [RTRIM и LTRIM]), таким образом, мы сразу удалили пробелы как в начале, так и в конце. Однако в новых версиях SQL сервер появилась функция TRIM, которая делает то же самое, т.е. использование двух функций RTRIM и LTRIM для удаления всех пробелов как в начале, так и в конце, теперь не требуется.

SELECT ProductName AS [Исходное значение],
       LTRIM(ProductName) AS [LTRIM],
       RTRIM(ProductName) AS [RTRIM],
       RTRIM(LTRIM(ProductName)) AS [RTRIM и LTRIM],
       TRIM(ProductName) AS [TRIM]
FROM Goods;

LOWER и UPPER

Следующие функции, которые стоит рассмотреть – это LOWER и UPPER, так как бывают случаи, что нужно значение всей строки получить в определённом регистре, в верхнем или в нижнем. Эти функции в языке T-SQL используются именно для этих целей.

• UPPER – возвращает все символы указанной строки в верхнем регистре;
• LOWER – возвращает все символы указанной строки в нижнем регистре.

В данном примере наглядно видно, как работают данные функции.

SELECT ProductName AS [Исходное значение],
       LOWER (ProductName) AS [LOWER],
       UPPER(ProductName) AS [UPPER]
FROM Goods;

LEN

Для того чтобы определить количество символов в строке, можно использовать функцию LEN.

Например.

SELECT ProductName AS [Исходное значение],
       LEN(ProductName) AS [LEN],
       LEN(LTRIM(ProductName)) AS [LTRIM и LEN]
FROM Goods;

Мы видим, сколько символов содержит каждая строка таблицы.

При этом стоит отметить, что пробелы в начале строки считаются символами и их учитывает функция LEN.

LEFT и RIGHT

Если Вам нужно получить не всю строку, а только первые несколько символов или последние несколько символов, можно использовать функции LEFT и RIGHT.

LEFT возвращает символы слева, RIGHT соответственно справа.

Эти функции принимают уже два параметра, первый – это строка, а второй – количество символов, которые нужно получить.

SELECT ProductName AS [Исходное значение],
       LEFT(ProductName, 3) AS [LEFT],
       RIGHT(ProductName, 3) AS [RIGHT],
       LEFT(LTRIM(ProductName), 3) AS [LEFT и LTRIM],
       RIGHT(RTRIM(ProductName), 3) AS [RIGHT и RTRIM]
FROM Goods;

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

SUBSTRING

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

В данном случае мы выводим часть строки, а именно всего 5 символов, начиная с 3 символа.

SELECT ProductName AS [Исходное значение],
       SUBSTRING(ProductName, 3, 5) AS [SUBSTRING]
FROM Goods;

Полный перечень строковых функций в Microsoft SQL Server

• LTRIM – возвращает строку после удаления начальных пробелов
• RTRIM – возвращает строку символов, из которой удалены все завершающие пробелы
• TRIM – удаляет символ пробела char(32) в начале и конце строки
• LOWER – возвращает строку, в которой символы верхнего регистра преобразованы в символы нижнего регистра
• UPPER – возвращает строку, в которой символы нижнего регистра преобразованы в символы верхнего регистра
• LEN – возвращает количество символов указанного строкового выражения, исключая конечные пробелы
• LEFT – возвращает указанное число символов символьного выражения слева
• RIGHT – возвращает указанное число символов символьной строки справа
• SUBSTRING – возвращает часть строки, начиная с указанной позиции, и определенное количество символов
• REPLACE – заменяет все вхождения указанного строкового значения другим строковым значением
• REPLICATE – повторяет значение строки указанное число раз
• REVERSE – возвращает строковое значение, где символы переставлены в обратном порядке справа налево
• CONCAT – функция возвращает строку, которая формируется в результате объединения двух или более строковых значений
• CONCAT_WS – функция возвращает строку, которая формируется в результате объединения двух или более строковых значений. Однако в отличие от CONCAT она разделяет значения в результирующей строке с помощью разделителя, указанного в первом аргументе функции
• ASCII – возвращает код ASCII первого символа указанного символьного выражения
• CHAR – функция преобразует код ASCII в значение символа
• CHARINDEX – функция выполняет поиск одной строки внутри другой строки, возвращая начальную позицию первого выражения, если оно найдено
• DIFFERENCE – функция возвращает целочисленную разницу между значениями SOUNDEX двух разных символьных выражений
• FORMAT – возвращает значение, отформатированное с использованием указанного формата, языка (необязательно) и региональных параметров
• NCHAR – возвращает символ Юникода с указанным целочисленным кодом, определенным в стандарте Юникода
• PATINDEX – для любого текстового типа данных возвращает начальную позицию первого вхождения шаблона в указанном выражении или нули, если шаблон не найден
• QUOTENAME – возвращает Юникод-строку с разделителями, образуя из строки ввода правильный идентификатор с разделителем SQL Server
• SOUNDEX – возвращает четырехсимвольный код (SOUNDEX) для оценки степени сходства двух строк
• SPACE – возвращает строку пробелов
• STR – возвращает символьные данные, преобразованные из числовых данных
• STRING_AGG – сцепляет значения строковых выражений, помещая между ними значения-разделители. В конце строки разделитель не добавляется
• STRING_ESCAPE – экранирует специальные символы в тексте и возвращает текст с экранированными символами. STRING_ESCAPE – это детерминированная функция
• STRING_SPLIT – функция с табличным значением, которая разбивает строку на строки подстрок в зависимости от указанного знака разделения
• STUFF – функция вставляет одну строку в другую. Она удаляет указанное количество символов первой строки в начальной позиции и вставляет на их место вторую строку.
• TRANSLATE – возвращает строку, предоставленную в качестве первого параметра, после преобразования символов, указанных во втором параметре, в конечный набор символов, указанный в третьем параметре
• UNICODE – возвращает целочисленное значение, соответствующее стандарту Юникод, для первого символа входного выражения