SUBSTRING (Transact-SQL)
Возвращает часть символьного, двоичного, текстового или графического выражения в SQL Server.
Синтаксис
SUBSTRING ( expression, start, length ) Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
выражение
Выражение типа character, binary, text, ntext или image.
Начало
Целое число или выражение типа bigint, указывающее начальную позицию возвращаемых символов. (Нумерация начинается с 1, то есть первый символ в выражении имеет позицию 1.) Если аргумент start имеет значение меньше 1, то возвращаемое выражение начинается с первого символа, который указан в аргументе expression. В этом случае количество возвращаемых символов является наибольшим значением либо суммы start + length– 1, либо 0. Если значение start больше количества символов в выражении значения, возвращается выражение нулевой длины.
length
Положительное целое число или выражение типа bigint, указывающее количество символов выражения expression, которое будет возвращено. Если значение length отрицательно, возникает ошибка и выполнение инструкции прерывается. Если сумма start и length больше количества символов в expression, то возвращается целочисленное выражение значения, начинающееся со значения start.
Типы возвращаемых данных
Возвращает символьные данные, если expression имеет один из поддерживаемых символьных типов данных. Возвращает двоичные данные, если аргумент expression имеет один из поддерживаемых двоичных типов данных. Возвращенная строка имеет тот же самый тип, как и заданное выражение. Исключения указаны в таблице.
| Заданное выражение | Возвращаемый тип |
|---|---|
| char/varchar/text | varchar |
| nchar/nvarchar/ntext | nvarchar |
| binary/varbinary/image | varbinary |
Замечания
Значения start и length должны быть указаны в виде количества символов для типов данных ntext, char или varchar и байтов для типов данных text, image, binary или varbinary.
Аргумент expression должен иметь тип varchar(max) или varbinary(max), если аргумент start или length содержит значение, превышающее 2 147 483 647.
Дополнительные символы (суррогатные пары)
При использовании параметров сортировки дополнительных символов (SC) и start, и length обрабатывают каждую суррогатную пару в expression как один символ. Дополнительные сведения см. в статье Collation and Unicode Support.
Примеры
А. Использование SUBSTRING с символьной строкой
Следующий пример показывает, как получить часть символьной строки. Из таблицы sys.databases этот запрос возвращает имена системных баз данных в первом столбце, первую букву имени базы данных во втором столбце и третий и четвертый символы в последнем столбце.
SELECT name, SUBSTRING(name, 1, 1) AS Initial , SUBSTRING(name, 3, 2) AS ThirdAndFourthCharacters FROM sys.databases WHERE database_id < 5; | name | Начальное | ThirdAndFourthCharacters |
|---|---|---|
| master | m | st |
| tempdb | t | mp |
| модель | m | de |
| msdb | m | db |
Далее показано, как можно вывести второй, третий и четвертый символ строковой константы abcdef .
SELECT x = SUBSTRING('abcdef', 2, 3); x ---------- bcd (1 row(s) affected) B. Использование SUBSTRING с данными типа text, ntext или image
Для выполнения приведенных ниже примеров необходимо установить базу данных pubs.
В приведенном ниже примере показано, как вернуть первые 10 символов из каждого столбца данных text и image в таблице pub_info базы данных pubs . Данные text возвращаются как varchar, а данные image — как varbinary.
USE pubs; SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo, SUBSTRING(pr_info, 1, 10) AS pr_info FROM pub_info WHERE pub_id = '1756'; pub_id logo pr_info ------ ---------------------- ---------- 1756 0x474946383961E3002500 This is sa (1 row(s) affected) В приведенном ниже примере показано влияние функции SUBSTRING на данные типов text и ntext. Во-первых, пример создает новую таблицу в базе данных pubs под именем npub_info . Во-вторых, пример создает столбец pr_info в таблице npub_info из первых 80 символов столбца pub_info.pr_info и добавляет ü в качестве первого символа. Наконец, с помощью предложения INNER JOIN извлекаются все идентификационные номера издателей, а также обработанные функцией SUBSTRING значения столбцов типа text и ntext со сведениями об издателях.
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'npub_info') DROP TABLE npub_info; GO -- Create npub_info table in pubs database. Borrowed from instpubs.sql. USE pubs; GO CREATE TABLE npub_info ( pub_id CHAR(4) NOT NULL REFERENCES publishers(pub_id) CONSTRAINT UPKCL_npubinfo PRIMARY KEY CLUSTERED, pr_info ntext NULL ); GO -- Fill the pr_info column in npub_info with international data. RAISERROR('Now at the inserts to pub_info. ',0,1); GO INSERT npub_info VALUES('0736', N'üThis is sample text data for New Moon Books, publisher 0736 in the pubs database') ,('0877', N'üThis is sample text data for Binnet & Hardley, publisher 0877 in the pubs databa') ,('1389', N'üThis is sample text data for Algodata Infosystems, publisher 1389 in the pubs da') ,('9952', N'üThis is sample text data for Scootney Books, publisher 9952 in the pubs database') ,('1622', N'üThis is sample text data for Five Lakes Publishing, publisher 1622 in the pubs d') ,('1756', N'üThis is sample text data for Ramona Publishers, publisher 1756 in the pubs datab') ,('9901', N'üThis is sample text data for GGG&G, publisher 9901 in the pubs database. GGG&G i') ,('9999', N'üThis is sample text data for Lucerne Publishing, publisher 9999 in the pubs data'); GO -- Join between npub_info and pub_info on pub_id. SELECT pr.pub_id, SUBSTRING(pr.pr_info, 1, 35) AS pr_info, SUBSTRING(npr.pr_info, 1, 35) AS npr_info FROM pub_info pr INNER JOIN npub_info npr ON pr.pub_id = npr.pub_id ORDER BY pr.pub_id ASC; Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
C. Использование SUBSTRING с символьной строкой
Следующий пример показывает, как получить часть символьной строки. Из таблицы dbo.DimEmployee данный запрос возвращает фамилию в одном столбце и первую букву имени в другом.
-- Uses AdventureWorks SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial FROM dbo.DimEmployee WHERE LastName LIKE 'Bar%' ORDER BY LastName; LastName Initial -------------------- ------- Barbariol A Barber D Barreto de Mattos P В приведенном ниже примере показано, как получить второй, третий и четвертый символы строковой константы abcdef .
USE ssawPDW; SELECT TOP 1 SUBSTRING('abcdef', 2, 3) AS x FROM dbo.DimCustomer; x ----- bcd Подстановочные знаки в SQL
В этой статье пойдет разговор о подстановочных символах в структурированном языке запросов SQL (structured query language). Понимание работы соответствующего оператора Like позволит вам выполнять специальные запросы и возвращать (return) искомые значения. Будут рассмотрены примеры для системы управления базами данных MS SQL Server.
Подстановочные знаки необходимы для замены любых символов в строке с последующим сравнением и выборкой нужных данных из таблицы. Они используются при составлении запроса. В декларативном языке программирования SQL для этих целей используется специальный оператор Like. В сочетании с ключевым словом WHERE, Like обеспечивает поиск заданного шаблона в необходимом столбце.
Изучив описание и список (List of wildcards) ниже, вы узнаете, какие подстановочные знаки можно использовать с оператором Like:
- «%» — может замещать собой любые значения (ноль и больше);
- «_» — нижнее подчеркивание означает лишь один символ;
- «[]» — здесь следует любой отдельный символ;
- «^» — тоже любой символ, но не заключенный в скобки;
- «-» — через дефис можно прописать целый набор символов, некий интересующий диапазон.
Выше мы рассмотрели подстановочные знаки для MS SQL Server — СУБД от Microsoft. Однако если сравнить системы SQL Server и Access, мы увидим, что схожим образом обстоит ситуация и в случае с базами данных MS Access — они тоже имеют свою систему подстановочных элементов — вот для сравнения List of wildcards для MS Access:
Также, глядя на вышеуказанные списки, стоит учесть, что все эти элементы можно применять в разнообразных комбинациях.
Однако давайте лучше перейдем к практике: займемся составлением простейших запросов и посмотрим, как Like выполняет возвращение (returning) искомых данных.
Работа Like на примерах MS SQL Server
Для демонстрации работы оператора Like воспользуемся таблицей Customer со следующим содержимым:
Составим инструкцию, которая вернет (returned) из таблицы клиентов (from customers) всех покупателей, имена которых начинаются с буквы «а»:
SELECT * FROM Customer
WHERE FirstName LIKE 'a%';
После сравнения и выборки данных клиентов останется всего двое, что соответствует действительности:
Теперь давайте выполним выборку покупателей, в именах которых содержатся буквы «ci». Местонахождение этих букв в слове в нашем случае значения не имеет — главное, чтобы они были:
Мы видим, что оператор Like возвращает (returns) 2 имени. Важно понимать, что не имеет значения, где именно эти символы, ведь % может означать и ноль, то есть указанные символы могут быть и в начале слова, и в середине, и в конце. Чтобы продемонстрировать это, выполним ту же команду, но уже для телефонов. Поместив в шаблон «2», мы увидим, что возвращаются (return) все номера, где встречается цифра 2, причем вне зависимости от места расположения этой двойки:
Теперь поработаем со знаком нижнего подчеркивания. Он означает один и только один любой символ. С его помощью сделаем выборку стран, названия которых заканчиваются на «exico»:
Также учтите, что регистр в составляемом шаблоне значения не имеет, то есть Like сравнивает и возвращает (return) значения без учета регистра:
Теперь немного изменим запрос и задействуем два символа подчеркивания:
После сопоставления данных и отработки запроса мы получим такой же результат.
Дальше — интереснее. Можно выбрать из таблицы все страны, которые начинаются на «S», «F» и «G». Тут пригодятся квадратные скобки и % — то есть мы используем уже комбинацию:
Как видите, все очень даже просто. В следующем предложении выберем уже диапазон значений из нужного столбца, воспользовавшись комбинацией трех подстановочных элементов:
То есть мы вывели все страны, названия которых начинаются с букв A, B или C.
Теперь давайте вспомним, что в программировании существует равно (==) и не равно (!=). По схожей аналогии работает и [charlist]. Если в начале квадратных скобок мы поместим восклицательный знак, произойдет выборка всех данных, которые не отвечают поставленному условию (not). Синтаксис следующий:
Благодаря этому запросу мы получим все города, названия которых НЕ начинаются с букв A, B или C. Но если вернуться к таблицам начала статьи, становится понятно, что это работает лишь для БД MS Access.
RIGHT (Transact-SQL)
Возвращает указанное число символов символьной строки справа.
Синтаксис
RIGHT ( character_expression , integer_expression ) Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
character_expression
Выражение символьных или двоичных данных. character_expression может быть константой, переменной или столбцом. character_expression может иметь любой тип данных, который может быть неявно преобразован в varchar или nvarchar, кроме text или ntext. В противном случае используйте функцию CAST для явного преобразования типа аргумента character_expression.
Если string_expression имеет тип binary или varbinary, RIGHT выполняет неявное преобразование в тип varchar и, следовательно, двоичные входные данные не сохраняются.
integer_expression
Положительное целое число, указывающее количество символов выражения character_expression, которое будет возвращено. Если аргумент integer_expression отрицателен, возвращается ошибка. Если аргумент integer_expression имеет тип bigint и содержит большое значение, аргумент character_expression должен иметь длинный тип данных, например varchar(max).
Типы возвращаемых данных
Возвращает значение типа varchar, если character_expression имеет символьный тип данных, отличный от Юникода.
Возвращает значение типа nvarchar, если character_expression имеет символьный тип данных Юникода.
Дополнительные символы (суррогатные пары)
При использовании параметров сортировки SC функция Right рассматривает суррогатную пару UTF-16 как один символ. Дополнительные сведения см. в статье Collation and Unicode Support.
Примеры
А. Применение функции RIGHT со столбцом
В следующем примере возвращаются пять правых символов первого имени для каждого пользователя в базе данных AdventureWorks2022.
SELECT RIGHT(FirstName, 5) AS 'First Name' FROM Person.Person WHERE BusinessEntityID < 5 ORDER BY FirstName; GO First Name ---------- Ken Terri berto Rob (4 row(s) affected) Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
B. Применение функции RIGHT со столбцом
В приведенном ниже примере возвращаются пять правых символов каждой фамилии в таблице DimEmployee .
-- Uses AdventureWorks SELECT RIGHT(LastName, 5) AS Name FROM dbo.DimEmployee ORDER BY EmployeeKey; Здесь приводится частичный результирующий набор.
Name ----- lbert Brown rello lters C. Применение функции RIGHT с символьной строкой
В приведенном ниже примере функция RIGHT используется для получения двух последних символов из символьной строки abcdefg .
SELECT RIGHT('abcdefg', 2); Обрезка строки в sql
В MySQL, например, есть две функции, которые могут помочь:
— INSTR — возвращает первую позицию подстроки в строке.
— LEFT — обрезает текст с начала до указанной позиции.
Поэтому можно сделать так (-1 нужен, чтобы в результат не включать пробел):
SET @str = 'Некоторая строка с пробелами'; SELECT LEFT(@str, INSTR(@str, ' ') - 1);Но, если в строке нет пробелов, то вернется строка с нулевой длиной. Для этого можно добавить условие, и если нет пробелов в строке — возвращать исходную строку.
SET @str = 'Некоторая_строка_без_пробелов'; SELECT IF( INSTR(@str, ' ') = 0, @str, LEFT(@str, INSTR(@str, ' ') - 1) );P.S.: Но, если Вам это нужно не для составления сложных вложенных запросов, то лучше такую обработку перенести на клиентский код.