Transact sql строковые функции. О строковых функциях SQL или как манипулировать текстом в базе данных MySQL

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

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

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

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

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

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

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

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

Тех, кто еще не в курсе генерации числовой последовательности, отсылаю к соответствующей статье .

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

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

Рассмотрим теперь задачу определения нахождения искомой подстроки в строковом выражении. Для этого могут использоваться две функции - CHARINDEX и PATINDEX . Обе они возвращают начальную позицию (позицию первого символа подстроки) подстроки в строке. Функция CHARINDEX имеет синтаксис:

CHARINDEX (искомое_выражение , строковое_выражение [, стартовая_позиция ])

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

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

Следующий пример определяет позиции первого и второго вхождения символа "a" в имени корабля "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:

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

Как Вы понимаете первый запрос это просто выборка всех строк (Основы 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 то на этом сайте мы не раз затрагивали эту очень интересную тему, например.

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

Результат - 11. Чтобы выяснить, какие это буквы, мы можем применить функцию CHAR, которая возвращает символ по известному ASCII-коду (от 0 до 255):

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

Тех, кто еще не в курсе генерации числовой последовательности, отсылаю к соответствующей статье.

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

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

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

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

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

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

Below is a full list of the string functions taken from BOL:

Let"s start from two reciprocally related functions - ASCII and CHAR:

The ASCII function returns an ASCII code value of the leftmost character of a character expression, being a function argument.

Here is an example of the way to determine how many different letters which the names of the ships in the Ships table start from are:

SELECT COUNT(DISTINCT ASCII(name)) FROM Ships

Result is 11. To find out what these letters are, we can use CHAR function that returns a character by a known ASCII code value (from 0 to 255):

SELECT DISTINCT CHAR(ASCII(name)) FROM Ships ORDER BY 1

We should note that the identical result can be got simpler using one more function - LEFT that has the following syntax:

LEFT (<character_expression >, <integer_expression >)

and returns the part of a character string starting at a specified number of characters from the left. So,

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

And this is the way to get the table of codes of all alphabetical characters:

SELECT CHAR(ASCII("a")+ num-1) letter, ASCII("a")+ num - 1 FROM

(SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x
CROSS JOIN
CROSS JOIN
) x
WHERE ASCII("a")+ num -1 BETWEEN ASCII("a") AND ASCII("z")

For those who are unaware of generating of numerical sequence I refer to the corresponding article .

It is known that code values of lowercase letters and capital letters differ. That"s why in order to get the whole set without rewriting above query; it will be enough to write up the identical code to aforesaid:

UNION
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")

Now let"s consider the task of searching a substring in a string expression. The two functions can be used for this: CHARINDEX and PATINDEX . They both return a starting position (a position of the first character of a substring) of a substring in a character string. The function CHARINDEX has the following syntax:

CHARINDEX (string_expression , target_expression [ , start_location ] )

Here an optional integer parameter start_location defines a position in a string_expression , where searching a target_expression starts from. If this argument is omitted, a searching starts from the beginning of a string_expression . For example, the query

SELECT name FROM Ships WHERE CHARINDEX("sh", name) > 0

will output the ships that has the sequence of characters "sh" in its names. Here we use the fact that if a substring to be found is not found, the function CHARINDEX returns 0. The result of executing the query will contain the following ships:

We should note that if a target_expression or string_expression is NULL, the result of the function is also NULL.

The next example determines the positions of the first and second occurrences of the character "a" in the name of the ship "California"

SELECT CHARINDEX("a",name) first_a,
CHARINDEX("a", name, CHARINDEX("a", name)+1) second_a

Pay attention that in determining of the second occurrence the starting position, used in function, is the position of a character next to the first "a" - CHARINDEX("a", name)+1. The propriety of the result - 2 and 10 - can be easily checked.

The function PATINDEX has the following syntax:

PATINDEX ("%pattern%" , string_expression )

The main distinction of this function from CHARINDEX is that a search string may contain wildcard characters - % and _. Leading and ending characters "%" are required. For, example, usage of this function in the first example will look like

SELECT name FROM Ships WHERE PATINDEX("%sh%", name) > 0

And there"s the way to find the names of the ships that contain the sequence of three characters, first and last of which is "e":

SELECT name FROM Ships
WHERE PATINDEX("%e_e%", name) >0

The result of executing of this query looks like this:

The function RIGHT that complements LEFT returns a specified number of characters from the right of a character expression:

RIGHT(<character_expression >,<integer_expression >)

Here is, for example, the way to determine the names of the ships that start and end with the same letter:

SELECT name FROM Ships
WHERE LEFT(name, 1) = RIGHT(name, 1)

The thing that we got an empty resulting set means that such ships are absent in our database. Let"s take a combination - a class and a name of a ship.

The combining of two string values into one is called concatenation , and in the SQL Server sign "+" is used for this operation ("||" in standard). So,

SELECT * FROM (
) x

Here we separate by space the class and the name of a ship. Besides, in order not to repeat the whole construction in the function argument, we use a subquery. The result will look like this:

And what if a string expression will contain only one character? The query will output it. You can easily check it by writing

SELECT * FROM (
SELECT class +" "+ name AS cn FROM Ships
UNION ALL SELECT "a" as nc
) x
WHERE LEFT(cn, 1)=RIGHT(cn, 1)

In order to exclude this case, one more useful function LEN(string_expression ) can be used. It returns the number of characters in a string. Let"s bound by the case, when the number of characters is greater than one:

SELECT * FROM (
SELECT class +" "+ name AS cn FROM Ships
UNION ALL SELECT "a" as nc
) x
WHERE LEFT(cn, 1)=RIGHT(cn, 1) AND LEN(cn)>1

There are tasks on the website that require to normalize (find maximum etc.) in a numerical order a values represented in a string format, for example, a seat number in a plane ("2d") or a speed of CD ("24x"). The problem lies in a fact that the text sorts like this (ascending)

11a
1a
2a

SELECT "1a" AS place
UNION ALL SELECT "2a"
UNION ALL SELECT "11a"
ORDER BY 1

If the sorting of places in order of rows increase is required, the order must be like this:

1a
2a
11a

In order to get such an order, the sorting by a numeric values in text is needed. I offer the following algorithm:
1. Extract a number from the string.
2. Convert it to a numeric format.
3. Perform a sorting by a converted value.

As we know that there"s only one letter in the string, and to extract a number we can use the following construction that doesn"t depend on a number of numerals in a seat number:

LEFT(place, LEN(place)-1)

If we just bound by this, we"ll get

Converting to a numeric format may look like the following:

CAST (LEFT(place, LEN(place)-1) AS INT)

Now we should just perform a sorting

SELECT * FROM (
SELECT "1a" AS place
UNION ALL SELECT "2a"
UNION ALL SELECT "11a"
) x ORDER BY CAST(LEFT(place, LEN(place)-1) AS INT)

As required to proof.

Previously we used the LEFT function to extract a number from a string expression, because we knew a priori how many characters should be removed from the right (one). And what if we should extract a string from a substring not by the known position of a character, but by a character itself? For example, extract all the characters before the first letter "x" (a value of CD speed).

In this case we can also use the previously considered CHARINDEX function that allows us to determine the unknown position of the character:

SELECT model, LEFT(cd, CHARINDEX("x", cd) -1) FROM PC

SUBSTRING function

SUBSTRING (<expression > ,<start >,<length >)

This function allows us to extract from an expression its part of a specified length, starting from a specified initial position. Expression may be a character or a binary string, and also can have a text or image type. For example, if we need to get 3 characters in a ship name, starting from the 2 nd character, then it"s not quite easy to do it without the SUBSTRING function. And so we write:

SELECT name, SUBSTRING(name, 2, 3) FROM Ships

In case we need to extract all the characters, starting from the certain, we also can use this function. For example,

SELECT name, SUBSTRING(name, 2, LEN(name)) FROM Ships

will give us all the characters in the names of the ships, starting from the second letter in the name. Pay attention that in order to specify the number of characters to extract I used the LEN(name) function that returns a number of characters in the name. It"s clearly that forasmuch as I need the characters, starting from the second, its number will be less than the whole number of the characters in the name. But it doesn"t cause an error, because if a specified number of characters exceed a permissible number, all the characters until the end of a string will be extracted. That is why I take it with a reserve sparing myself the calculations.

REVERSE function

This function reverses a string, just like reading it from the right to left. That is the result of the query

SELECT REVERSE("abcdef")

will be "fedcba". In case of the absence of the RIGHT function in the language the query

SELECT RIGHT("abcdef",3)

can be equally replaced with the query

SELECT REVERSE(LEFT(REVERSE("abcdef"),3))

I see the profit of this function in the following. Let we need to determine a position not the first, but the last occurrence of any character (or a sequence of characters) in a character string.

Let"s remind an example, where we were determining the position of the first character "a" in the name of the ship "California":

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

Now let"s determine the position of the last occurrence of the character "a" in this name. The function

CHARINDEX("a", REVERSE(name))

will allow us to find this position, but from the right. In order to get the position of this character, but from the left, it"s enough to write

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

REPLACE function

REPLACE (<string_expression1 > , <string_expression2 > , <string_expression3 >)

This function replaces in the string_expression1 all the occurrences of the string_expression2 with the string_expression3 . This function is absolutely helpful in UPDATE operators, if we should change the content of the column. For example, let we are needed to replace all the spaces with the hyphen in the names of the ships. Then we can write

UPDATE Ships
SET name = REPLACE(name, " ", "-")

(This example you can perform on the page with the DML exercises, where the questions on updating the data are admitted)

However this function can also find its appliance in more non-trivial cases. Let"s determine how many times the letter "a" is used in a name of a ship. The idea is quite easy: let"s replace every searching letter with any two characters, and after that we"ll calculate the difference of lengths of the string we got and the source string. So,

SELECT name, LEN(REPLACE(name, "a", "aa")) - LEN(name) FROM Ships

And what if we need to determine the number of occurrences of a random sequence of characters that, say, we get as a parameter of a stored procedure? In this case we should complement the used above algorithm with the division on a number of a characters in a sequence:

DECLARE @str AS VARCHAR(100)
SET @str="ma"
SELECT name, (LEN(REPLACE(name, @str, @str+@str)) - LEN(name))/LEN(@str) FROM Ships

Here in order to double the number of searching characters the concatenation @str+@str was applied. However for this effect we can use one more function - REPLICATE that repeats the first argument so many times as it specified by the second argument.

SELECT name, (LEN(REPLACE(name, @str, REPLICATE(@str, 2))) - LEN(name))/LEN(@str) FROM Ships

That is we repeat twice the substring stored in the variable @str.

If we need to replace in a string not a certain sequence of characters, but a specified number of characters, starting from some position, it"s simpler to use the STUFF function:

STUFF (<character_expression1 > , <start > , , <character_expression2 >)

This function replaces a substring with length of length that starts from the start position in the character_expression1 with the character_expression2 .

The example. Change the name of a ship: append "_" (underscore) and a launching year after the first 5 characters. If there is less than 5 characters in the name, complement it with the spaces.

This task can be solved with many different functions. We"ll try to do it with the STUFF function.

In the first approach we"ll write (bounding with the select query):

SELECT name, STUFF(name, 6, LEN(name), "_"+launched) FROM Ships

I use LEN(name) as the third argument (number of characters to replace), because I should replace all the characters until the end of the string, and that"s why I take it with a reserve - the original number of characters in the name. But this query will still return an error. And the question is not in the third argument, but in the forth, where the concatenation of the string constant and the integer column is performing. This is the type conversion error. In order to convert a number to its string representation we may use one more built-in function - STR :

STR (<float_expression > [ , <length > [ , <decimal > ] ])

Under this conversion the round-up is being worked, and a length specifies a length of the resulting string. For example,

STR(3.3456, 5, 1) 3.3
STR(3.3456, 5, 2) 3.35
STR(3.3456, 5, 3) 3.346
STR(3.3456, 5, 4) 3.346

Take note, that if have got string view of the number is less than the specified length, the leading spaces are added. If the result is greater than the specified length, the fractional part is being truncated (with the round-up); in case there is an integer value we get the corresponding number of the asterisks "*".

STR(12345,4,0) ****

By the way, default length is 10 symbols. Keeping in mind that a year is represented by four numbers, we will write

SELECT name, STUFF(name, 6, LEN(name), "_"+STR(launched, 4)) FROM Ships

Everything is almost right. The case is rested to consider, when the number of symbols in the name is less than 6, as in this case the function STUFF gives NULL. Well, we will have to be on the rack to the end, which is connected with using this function in this example, on the way applying one more string function.

We"ll add the trailing blanks for the name`s length being knowingly more than 6. There is a special SPACE function for that

SPACE(<number_of_blanks >):

SELECT name, STUFF(name + SPACE(6), 6, LEN(name), "_"+STR(launched,4)) FROM Ships

LTRIM and RTRIM functions

LTRIM (<character_expression >)

RTRIM (<character_expression >)

cut accordingly the leading and trailing blanks of the string expression, which is being converted by default to the VARCHAR type.

Let us need to build such a string: passenger`s name_passenger`s identifier for every record from the Passenger table. If we write

SELECT name + "_" + CAST(id_psg AS VARCHAR) FROM Passenger,

we will get in the result something like:

It is connected with the fact that the column name has the type CHAR (30). The short string is added with the blanks for this type to the specified dimension (we have 30 symbols). The function RTRIM will help us here:

SELECT RTRIM(name) + "_" + CAST(id_psg AS VARCHAR) FROM Passenger

LOWER and UPPER functions

LOWER(<character_expression >)

UPPER(<character_expression >)

convert all the characters of the argument accordingly to the lower and upper register. These functions turn out to be useful in comparison with the registerdepended strings.

A pair of the interesting functions SOUNDEX and DIFFERENCE

SOUNDEX(<character_expression >)

DIFFERENCE (<character_expression_1 >, <character_expression_2 >)

allow us to determine the similar-sounding of words. SOUNDEX returns a four-character code to evaluate the similarity, but DIFFERENCE just properly evaluates the similarity of two comparing character strings. Being that these functions do not support Cyrillic, I refer interested to BOL for the examples of its usage.

In conclusion we will adduce some functions and a few examples of the Unicode usage:

UNICODE function

UNICODE (<ncharacter_expression >)

returns the Unicode value for the first character of the input expression.

NCHAR function

NCHAR (<integer >)

returns a character with the given integer Unicode value. A few examples:

SELECT ASCII("а"), UNICODE("а")

Returns the ASCII-code value and the Unicode value of the Russian letter "а": 224 and 1072.

SELECT CHAR(ASCII("а")), CHAR(UNICODE("а"))

We try to recover a symbol by its code value. We get "а" and NULL. The NULL-value returns because the 1072 code value is absent in the usual code table.

SELECT CHAR(ASCII("а")), NCHAR(UNICODE("а"))

Now it"s all right, "a" in both cases.

SELECT NCHAR(ASCII("а"))

will give "a", because the Unicode value 224 exactly corresponds to this letter.

» Given examples here can be done directly on the website by selecting the check box “Without checking” on the page with SELECT exercises .