Как выделить часть строки в sql

SUBSTRING (Transact-SQL)

Returns part of a character, binary, text, or image expression in SQL Server.

Syntax

SUBSTRING ( expression, start, length ) 

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

Arguments

expression
Is a character, binary, text, ntext, or image expression.

start
Is an integer or bigint expression that specifies where the returned characters start. (The numbering is 1 based, meaning that the first character in the expression is 1). If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length— 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned.

length
Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

Return Types

Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types. The returned string is the same type as the specified expression with the exceptions shown in the table.

Specified expression	Return type
char/varchar/text	varchar
nchar/nvarchar/ntext	nvarchar
binary/varbinary/image	varbinary

Remarks

The values for start and length must be specified in number of characters for ntext, char, or varchar data types and bytes for text, image, binary, or varbinary data types.

The expression must be varchar(max) or varbinary(max) when the start or length contains a value larger than 2147483647.

Supplementary Characters (Surrogate Pairs)

When using supplementary character (SC) collations, both start and length count each surrogate pair in expression as a single character. For more information, see Collation and Unicode Support.

Examples

A. Using SUBSTRING with a character string

The following example shows how to return only a part of a character string. From the sys.databases table, this query returns the system database names in the first column, the first letter of the database in the second column, and the third and fourth characters in the final column.

SELECT name, SUBSTRING(name, 1, 1) AS Initial , SUBSTRING(name, 3, 2) AS ThirdAndFourthCharacters FROM sys.databases WHERE database_id < 5; 

Here is the result set.

name	Initial	ThirdAndFourthCharacters
master	m	st
tempdb	t	mp
model	m	de
msdb	m	db

Here is how to display the second, third, and fourth characters of the string constant abcdef .

SELECT x = SUBSTRING('abcdef', 2, 3); 

Here is the result set.

x ---------- bcd (1 row(s) affected) 

B. Using SUBSTRING with text, ntext, and image data

To run the following examples, you must install the pubs database.

The following example shows how to return the first 10 characters from each of a text and image data column in the pub_info table of the pubs database. text data is returned as varchar, and image data is returned as 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'; 

Here is the result set.

pub_id logo pr_info ------ ---------------------- ---------- 1756 0x474946383961E3002500 This is sa (1 row(s) affected) 

The following example shows the effect of SUBSTRING on both text and ntext data. First, this example creates a new table in the pubs database named npub_info . Second, the example creates the pr_info column in the npub_info table from the first 80 characters of the pub_info.pr_info column and adds an ü as the first character. Lastly, an INNER JOIN retrieves all publisher identification numbers and the SUBSTRING of both the text and ntext publisher information columns.

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; 

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

C. Using SUBSTRING with a character string

The following example shows how to return only a part of a character string. From the dbo.DimEmployee table, this query returns the last name in one column with only the first initial in the second column.

-- Uses AdventureWorks SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial FROM dbo.DimEmployee WHERE LastName LIKE 'Bar%' ORDER BY LastName; 

Here is the result set.

LastName Initial -------------------- ------- Barbariol A Barber D Barreto de Mattos P 

The following example shows how to return the second, third, and fourth characters of the string constant abcdef .

USE ssawPDW; SELECT TOP 1 SUBSTRING('abcdef', 2, 3) AS x FROM dbo.DimCustomer; 

Here is the result set.

x ----- bcd 

Обрезка строки в 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.: Но, если Вам это нужно не для составления сложных вложенных запросов, то лучше такую обработку перенести на клиентский код.

Функция SUBSTRING

Функция SUBSTRING (выражение, начальная позиция, длина) позволяет извлечь из выражения его часть заданной длины, начиная от заданной начальной позиции. Выражение может быть символьной или бинарной строкой, а также иметь тип text или image. Например, если нам потребуется получить три символа в названии корабля, начиная со второго символа, то сделать это без помощи функции SUBSTRING будет не просто. А так мы напишем:

Консоль

Выполнить

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

Консоль

Выполнить

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

2.25. Функции работы со строками

У SQL сервера достаточно много мощных функций для работы со строками и в этом разделе мы рассмотрим наиболее интересные и часто используемые из них. Из моего личного опыта (ваши задачи могут дать другой результат), наиболее часто используемой является функция SUBSTRING. Именно с нее мы и начнем.

SUBSTRING

Помниться, что мы добавили к значениям в колонке имен работников префикс 'mr.' (см. разд. 2.17). А как теперь от него избавится во время обращения к таблице? Достаточно просто, если воспользоваться функцией SUBSTRING, которая возвращает указанную часть строки. Этой функции необходимо передать три параметра:

1. Поле, часть строки которого нужно получить;
2. Первый символ;
3. Количество интересующих нас символов.

Посмотрим, как вышесказанное можно реализовать в виде запроса:

SELECT idPeoples, CASE SUBSTRING(vcFamil, 1, 3) WHEN 'mr.' THEN SUBSTRING(vcFamil, 4, 255) ELSE vcFamil END FROM tbPeoples

В этом примере, мы выбираем только два поля: "idPeoples" и поле, результат которого зависит от проверки CASE. В данном случае CASE проверяет результат работы функции SUBSTRING, которая выбирает символы из поля "vcFamil" начиная с первого по третий. Если результат равен 'mr.', то необходимо обрезать этот префикс.

Для того, чтобы отбросить ненужные символы от значения поля, мы снова пользуемся функцией SUBSTRING, но теперь выбираем символы, начиная с четвертного (начиная с первого, после 'mr.'). В качестве количества символов я указал число 255, что больше максимального значения поля, а значит, строка будет выбрана до конца, начиная 4-го.

Теперь попробуем обновить данные в таблице, чтобы в поле "vcName", чтобы в нем не было лишних символов 'mr.'. Для этого выполняем следующий запрос:

UPDATE tbPeoples SET vcFamil=(case SUBSTRING(vcFamil, 1, 3) WHEN 'mr.' THEN SUBSTRING(vcFamil, 4, 255) ELSE vcFamil END)

В этом примере полю "vcName"присваивается результат сравнения CASE, который мы уже рассмотрели выше. Таким образом, мы избавились от лишних букв в фамилиях.

LEFT

Задачу обрезание лишних символов из начала строки можно было бы решить и с использованием функции LEFT, которая возвращает указанное количество символов, начиная с 1-го. Функции нужно передать следующие два параметра:

1. Поле, подстроку которого нужно получить;
2. Количество символов.

Следующий пример формирует ФИО, в котором имя и отчество сокращены:

SELECT vcFamil+' '+left(vcName, 1)+'. '+left(vcSurName, 1)+'.' FROM tbPeoples

Поле "vcFamil" выводится полностью, а вот от имени и отчества выводится только один левый (первый) символ.

Теперь посмотрим, как можно было использовать LEFT для обрезания префикса 'mr.':

UPDATE tbPeoples SET vcFamil=(case LEFT(vcFamil, 3) WHEN 'mr.' THEN SUBSTRING(vcFamil, 4, 255) ELSE vcFamil END)

LEN

Функция LEN позволяет определить длину строки или значения поля. Функции достаточно передать строку или имя поля, длина значений которого нас интересует. Например, следующий запрос отобразить длину всех значений в поле "vcFamil":

SELECT vcFamil, len(vcFamil) FROM tbPeoples

В следующем примере мы ищем записи, в которых фамилия состоит 7-и символов:

SELECT vcFamil FROM tbPeoples WHERE len(vcFamil)=7

LOWER

Если ваш сервер настроен так, что строки чувствительные к регистру букв, то с поиском по строковым полям могут быть серьезные проблемы. Если вы указали фамилию как Иванов, то это значение не будет равно ИВАНОВ, а значит, мы не увидим необходимую запись. Проблему решает функция LOWER, которая приводит указанную строку к нижнему регистру.

Рассмотрим пример. В следующем запросе мы выбираем все фамилии, при этом они отображаются в нижнем регистре (маленькими буквами):

SELECT LOWER(vcFamil) FROM tbPeoples

Теперь посмотрим на следующий пример:

SELECT * FROM tbPeoples WHERE LOWER(vcFamil)=LOWER('Сидоров')

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

UPPER

Функция Upper также изменяет регистр букв, только делает их все большими. Это значит, что функцию можно также использовать для сравнения двух строк разного регистра, если все буквы привести к большим:

SELECT * FROM tbPeoples WHERE UPPER(vcFamil)=UPPER('Сидоров')

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

LTRIM и RTRIM

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

Иванов Иванов

Когда смотришь на эти фамилии, то видно, что вторая строка сдвинута вправо за счет пробела вначале. Это значит, что база данных будет воспринимать эти значения по-разному. Чтобы избавится от лишних пробелов, как раз используют функции LTRIM и RTRIM. Например:

SELECT * FROM tbPeoples WHERE LTRIM(vcFamil)=LTRIM(' Сидоров')

В этом примере поле "vcFamil" сравнивается с фамилией Сидоров, с пробелом в начале. Чтобы убрать пробел используется функция LTRIM. В следующем примере мы убираем и левые и правые пробелы:

-- Убрать лишние пробелы SELECT * FROM tbPeoples WHERE vcFamil=LTRIM(RTRIM(' Сидоров '))

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

SELECT * FROM tbPeoples WHERE vcFamil='Сидоров '

Если работник с фамилией Сидоров (без пробелов в конце) существует в таблице, и запрос отобразил его, то сервер автоматически убрал пробел.

PATINDEX

С помощью функции PATINDEX можно искать часть подстроки по определенному шаблону. Допустим, что нам надо найти все фамилии, в которых есть две буквы "о", между которыми может находиться любой символ. Эту задачу можно решить с помощью следующего запроса:

SELECT vcFamil, PATINDEX('%О_О%', vcFamil) FROM tbPeoples

Если посмотреть на функцию, то пока не понятно, чем она отличается от LIKE с шаблоном? Все очень просто – LIKE используется для создания ограничений в секции WHERE, а PATINDEX возвращает индекс символа, начиная с которого идет указанный шаблон в строке. Если бы мы использовали LIKE, то сервер вернул бы нам только те строки, где найден шаблон:

SELECT vcFamil FROM tbPeoples WHERE vcFamil LIKE '%О_О%'

Если использовать функцию PATINDEX, то в результат попадут все строки (мы не ограничиваем вывод в секции WHERE), но там где в фамилии нет шаблона, в соответствующей строке будет стоять ноль, а там где есть, будет стоять 1. Посмотрим на пример результата выполнения запроса с использованием функции PATINDEX:

vcFamil Ind ----------------------------------------------- ПОЧЕЧКИН 0 ПЕТРОВ 0 СИДОРОВ 4 КОНОНОВ 2 СЕРГЕЕВ 0

В данном примере шаблон '%О_О%' присутствует в фамилии Сидоров. Начиная с четвертого символа идут буквы "оро".

REPLACE

Функция replace позволяет найти в значении поля подстроку и заменить ее на новое значение. У этой функции три параметра:

1. Строка, в которой нужно искать подстроку;
2. Подстрока, которую ищем;
3. Значение, которое нужно подставить.

Посмотрим пример использования этой функции:

SELECT vcFamil, REPLACE(vcFamil, 'оро', 'аро') AS Ind FROM tbPeoples WHERE PATINDEX('%О_О%', vcFamil)>0

Мы выбираем из таблицы два поля: фамилию и результат функции REPLACE. Функция ищет в поле "vcFamil" строку "оро" и заменяет ее на строку "аро". Чтобы лучше было понятно, посмотрим на результат работы функции:

vcFamil Ind ---------------------------------------------- СИДОРОВ СИДароВ КОНОНОВ КОНОНОВ КОРОВА КароВА МОЛОТКОВ МОЛОТКОВ САДОВОДОВ САДОВОДОВ СОДОРОЧКИН СОДароЧКИН (6 row(s) affected)

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

REPLICATE

С помощью функции REPLICATE можно размножать строку. У функции два параметра:

1. Строка или имя поля, которое нужно вывести несколько раз;
2. Количество необходимых повторений

Переходим к примеру. Следующий запрос выводит в результирующий набор дважды значение поля фамилии:

SELECT REPLICATE(vcFamil, 2) FROM tbPeoples

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

ПОЧЕЧКИНПОЧЕЧКИН ПЕТРОВПЕТРОВ СИДОРОВСИДОРОВ КОНОНОВКОНОНОВ СЕРГЕЕВСЕРГЕЕВ ВАСИЛЬЕВВАСИЛЬЕВ .

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

SELECT REPLICATE('=', 50)

Красиво? А главное удобно в управлении.

REVERSE

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

SELECT REVERSE(vcFamil) FROM tbPeoples

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

SELECT REPLACE(vcFamil, LEFT(vcFamil, 2), REVERSE(LEFT(vcFamil, 2)) ) FROM tbPeoples

Пример достаточно интересен тем, что лишний раз показывает, как использовать уже известные нам функции работы со строками. В результирующем наборе отображается результат работы функции REPLACE. Функции нужно передать:

1. Название поля, где хранится фамилия;
2. Первые два символа. Для получения первых двух символов используем уже знакомую нам функцию LEFT;
3. В качестве строки, которая должна будет поставлена вместо первых двух символов фамилии, выступают те же два символа, только перевернутые.

SPACE

С помощью функции SPACE можно создавать пробелы. В качестве единственного параметра нужно указать число, которое определяет количество возвращаемых пробелов. Работа функции идентична REPLICATE, если в качестве клонируемого символа указать пробел.

Допустим, что нам нужно вывести на экран поля фамилию и имя, разделенные 5-ю пробелами. Можно сделать так:

SELECT vcFamil+' '+vcName FROM tbPeoples

А можно воспользоваться функцией SPACE:

SELECT vcFamil+SPACE(5)+vcName FROM tbPeoples

Зачем нужна функция, когда можно воспользоваться без нее? Допустим, что вам нужно использовать 5 пробелов в нескольких местах большого сценария. Все легко решается без функций, но в последствии оказалось, что количество пробелов должно быть не 5, а 10. Придется пересматривать весь сценарий и корректировать пробелы. А если бы мы использовали SPACE в сочетании с переменными, то проблема решилась бы намного проще.

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

DECLARE @sp int SET @sp=10 SELECT vcFamil+SPACE(@sp)+vcName+SPACE(@sp)+vcSurName FROM tbPeoples

Теперь, достаточно только изменить значение переменной, и количество пробелов изменено во всем сценарии. А главное – что количество пробелов может быть определено динамически, на основе запросов к таблице.

STR

С помощью функции STR можно форматировать дробные числа в строку. Чем это отличается от преобразования типов? Тип остается тем же, а на экран мы выводим строку в нужном виде. Функции нужно передать три параметра:

1. Дробное число, которое нужно форматировать;
2. Общее количество символов, включая числа до и после запятой, пробелы и знак;
3. Количество знаков после запятой.

Допустим, что нам нужно вывести название и цену товара. Но цена имеет тип money, который содержит слишком большое количество нулей. Чтобы избавиться от лишних чисел после запятой и получить строку, можно сначала привести тип money к типу number(10, 2), а потом результат привести к строке. Но можно решить все одной командой форматирования STR:

SELECT [Название товара], STR(Цена, 10, 2) FROM Товары

Выполните этот запрос и обратите внимание, что второе поле (отформатированная цена) выровнена вправо:

Название товара -------------------------------------------------- ---------- КАРТОФЕЛЬ 13.60 Сок 23.00 Шоколад 25.00 Хлеб 6.00 Сок 18.40 .

Выравнивание происходит из-за второго параметра – числа 10. Мы задали общее число символов, и выравнивание будет происходить по правой позиции указанного значения. Если второй параметр равен 10, а число состоит из 4 символов, то в начало результирующей строки будет добавлено 6 пробелов. Учитывайте это, при использовании функции STR.

STUFF

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

1. Строка, которую нужно изменить;
2. Позиция, в которую должна произойти вставка;
3. Количество удаляемых символов;
4. Вставляемая строка.

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

Рассмотрим пример, в котором цена вставляется в поле названия товара, начиная с первой позиции, не удаляя ни одного из символов:

SELECT STUFF([Название товара], 1, 0, STR(Цена, 10, 2)+' ') FROM Товары

Результат работы функции будет следующим:

-------------------------------------------- 13.60 КАРТОФЕЛЬ 23.00 Сок 25.00 Шоколад 6.00 Хлеб 18.40 Сок 12.00 Молоко 6.00 Хлеб .

На этом примере более наглядно видно, что вставляемая цена выравнивается вправо. Так как мы указали в функции STR количество символов равное 10, то вставляется не реальный размер цены, а именно 10 символов.

Попробуйте увеличить третий параметр до 1. В этом случае, первый символ в названии товара будет удален, а вместо него будет вставлена цена.