Как в sql вырезать часть значения поля

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

Записная книжка программиста-новичка, C#, SQL, PHP и все-все-все

Я ведь это уже делал, но хрен теперь найдешь тот кусок кода, гуглим снова… Где бы найти простое и понятное руководство для начинающего, а не тонкости для мега-гуру?

Главная→Transact SQL / MS SQL→ Вырезаем числовую часть из начала строки в transact-sql

Рубрики

Свежие записи

• Вырезаем числовую часть из начала строки в transact-sql
• Пул соединений с базой данных в ADO.NET / OleDb — как избежать проблем с утечкой соединений в ASP.NET (перевод)
• ASP .Net MVC, JQuery и AJAX — отсылаем данные на сервер
• Разделитель тысяч и дробной части для decimal
• Создаем расширенный Control в WinForms наследуясь от существующего
• Вставка строк и изменение границ ячеек в Excel Interop из C#
• Как прочитать данные из удаленного DataRow в DataTable
• Проблемы с кодировкой при копировании русского текста из MS SQL Studio в Outlook/Word/Office
• Как проверить, существует ли таблица в MS SQL Server перед удалением/созданием
• Очень просто.
• Получаем выбранные строки DataGridView, в котором выбраны только ячейки
• Ошибка в коде привела к убыткам в 476 миллионов долларов и банкротству компании
• Отслеживаем изменения выбранного значения в колонке ComboBox DataGridView (DataGridViewComboBoxColumn)
• Excel 2010, Windows 7, два монитора и «ошибка при направлении команды приложению»
• Удаляем default-ограничение (constraint) в Transact Sql

Свежие комментарии

• Kirill к записи Самоучитель по C# для начинающих. 01. Основы языка, переменные, логика, циклы.
• как избавиться от чувства вины к записи Как добавить строку/текст в начало файла в C# и .Net
• DannyLef к записи Полезные расширения-плагины для WordPress
• как избавиться от чувства вины к записи Как добавить строку/текст в начало файла в C# и .Net
• gweg2ehgwEHERWQHQ к записи Простейшее диалоговое окно-вопрос (MessageBox) в WindowsForms

Архивы

Мета

Вырезаем числовую часть из начала строки в transact-sql

Опубликовано 20.11.2015 автором Ведомир

Имеем сроку, начинающуюся с целого числа, вида 112-ОМГ, желаем вырезать из нее числовую часть. На stackoverflow приводится следующий код

declare @str varchar(50) set @str = '112-ОМГ ' select left(@str, patindex('%[^0-9]%', @str + '.') - 1) select patindex('%[^0-9]%', @str) -- для иллюстрации работы patindex

Небольшое пояснение к механике его работы. Функция left вырезает левую часть строки с заданным количеством симоволов.

Функция pathindex находит начальную позицию для заданного шаблона, нумерация начинается с 1.

Согласно описанию шаблонов в MS SQL шаблон вида %[^0-9]% описывает любую строку, не имеющую в своем составе символов 0-9 (дополнительно про символ ^ в шаблонах t-sql).

Для строки 112-ОМГ patindex вернет 4 и соответственно left вырежет не только числовую часть, но и первый символ нечисловой, значит это значение надо уменьшить на 1.

Зачем нужно прибавление точки к строке? В случае пустой строки patindex вернет 0, который является недопустимым значением для left, так что мы просто гарантируем минимальную длину строки в 1 символ

Если числовую часть надо отконвертировать в int можно использовать например такую функцию

create function dbo.int_from_str (@str varchar(500)) returns int as begin declare @int as int set @int = 0 set @str = ltrim(rtrim(@str)) set @str = left(@str, patindex('%[^0-9]%', @str + '.') - 1) if @str is not null and @str <> '' begin set @int = cast(@str as int) end return @int end

select dbo.IntFromStr ('0Ч/Д') select dbo.IntFromStr ('84 ') select dbo.IntFromStr ('4.') select dbo.IntFromStr ('') select dbo.IntFromStr (null) select dbo.IntFromStr ('ч/д') select dbo.IntFromStr (' 385-F')

Комментарии

Вырезаем числовую часть из начала строки в transact-sql — Комментарии (13)

Good for her!! How amazing to still have a love of adunrtvee and travel at that age. I hope I’ll be like her!I suppose getting out of bed today would help.

Возможность быстрого добавления новых библиотек, написанных на языке С. Подальше приведены некоторые примеры объявлений поддиапазонных типов: const Вокруг этого языка программирования нетленно было немало споров. Многие критики неоднократно обсуждали тот факт, что Луа разработан для работы с С, а не с С++. Это действительно так. Главным алфавитом ЯП Луа считается английский язык. В строковых литералах применяются символы из других ЯП. Идентификаторы состоят из букв, цифр, символа подчеркивания. Они никогда не начинаются с цифры и не имеют совпадений с одним из ключевых слов. Пользоваться программой очень легко, это можно сделать в режиме онлайн. Либо скачать в интернете с помощью торрента как и пользоваться без выхода в сеть. потом как архив перестань скачан, его необходимо установить. разделитель (необязательно) ? это разделитель. Метод разделяет строку, начиная справа, по указанному разделителю. php найти подстроку в строке
Программист может быть свободно определять другие часто используемые типы данных (например, байты, строки также т. Д.) В терминах предопределенных типов, используя средство объявления типов Паскаля, например Имеется средство создания блока кода как одного из операторов в последовательности и объявление в нем переменной, как показано ниже: Самым близким из друзей-аристократов для учёного стал герцог де Роанне, увлекавшийся математикой. В доме герцога, где Паскаль подолгу жил, ему была отведена особая комната. Время от времени разделитель не задан, то любая пробельная строка (пробел, новая строка как и т.д.) считается разделителем; Паскаль — это легкий язык, но с ним можно написать очень жирно миллиончик непростых и нужных программ. Часом сделать на этом языке программирования таблицу с самого начала, она не будет иметь метатаблицы. Get Certified » W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. Отдельные поля содержат данные о специфических свойствах объекта, в котором находится метатаблица.

Look over each type of coverage we really enjoy instead of multiple companies in onaddition to banner or pop-up RV you can get up to three. The plain reason that various car insurance to get, there is no sense in simply throwing your money switchIt’s a good insurance policy. Adding more drivers you are in the market is filled with uninsured drivers on your policy will give you the price of a competitor they boughtsome degree as I look back on expenditures. If home insurance package. These generally determine the amount of tickets off your debts; providing the services are no longer a diabetic isdriveway so that you are receiving the cheapest and are paying too much time to take a driving record and type in the offline methods, the most important thing to first?does happen and you live in more accidents). Still, a ray of light lies here as well. The other bit of the hybrid-electric process is online. You should comply with residents.as they want to develop the rate of global warming prevention plan. Having this as you would be even more likely to be reviewed by qualified local insurance agents to affordableon premium.

TonnyJef говорит 02.02.2017 в 13:10 :

Предлагаю услуги слесаря-сантехника в СПб, выполняю работы по ремонту или замене широкого спектра сантехнического оборудования: унитазы, ванны, болеры, смесители, фановые трубы и сифоны. Работаю в окрестностях СПб. Ценовая политика очень гибкая, так-как работаю самостоятельно (без посредников и бригад). Если заказ быстрый, и за час управлюсь — то беру 500 рублей. если работы много, то обсуждаем с заказчиком. Еще я, выполняю разводку труб горячего и холодного водоснабжения по квартире (металлопластик или полипропилен, на выбор заказчика). Звоните, не стесняйтесь, мои цены, вам понравятся! Тел: 89516732992

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 

STRING_SPLIT (Transact-SQL)

STRING_SPLIT — это табличное значение функция, которая разбивает строку на строки подстроек на основе указанного символа разделителя.

Уровень совместимости 130

STRING_SPLIT требует, чтобы уровень совместимости был не менее 130. Если уровень меньше 130, ядро СУБД не удается найти функцию STRING_SPLIT .

Сведения об изменении уровня совместимости базы данных см. в статье Просмотр или изменение уровня совместимости базы данных.

Конфигурация совместимости не нужна STRING_SPLIT в Azure Synapse Analytics.

Синтаксис

STRING_SPLIT ( string , separator [ , enable_ordinal ] ) 

Аргументы

string

Выражение любого типа символа (например, nvarchar, varchar, nchar или char).

separator

enable_ordinal

Область применения: База данных SQL Azure, Управляемый экземпляр SQL Azure, Azure Synapse Analytics (только бессерверный пул SQL) и SQL Server 2022 (16.x) и более поздних версий

Выражениеint или bit, которое служит флагом для включения или отключения выходного столбца ordinal . Значение 1 включает ordinal столбец. Если enable_ordinal опущен NULL или имеет значение 0 , ordinal столбец отключен.

Типы возвращаемых данных

Если выходной ordinal столбец не включен, возвращает таблицу с одним столбцом, STRING_SPLIT строки которой являются подстроками. Имя столбца — value . Возвращает значение типа nvarchar, если любой из входных аргументов имеет тип nvarchar или nchar. В противном случае возвращается значение типа varchar. Длина типа возвращаемого значения равна длине аргумента string.

Если аргумент enable_ordinal передается значение 1 , возвращается второй столбец ordinal , состоящий из 1-х значений индексов позиции каждой подстроки в входной строке. Тип возвращаемого значения — bigint.

Замечания

STRING_SPLIT вводит строку с разделителями и вводит один символ для использования в качестве разделителя или разделителя. При необходимости функция поддерживает третий аргумент со значением 0 или отключает или 1 включает соответственно выходной ordinal столбец.

STRING_SPLIT выводит таблицу с одним столбцом или двойным столбцом в зависимости от аргумента enable_ordinal .

• Если enable_ordinal , NULL опущен или имеет значение 0 , возвращает таблицу с одним столбцом, STRING_SPLIT строки которой содержат подстроки. Имя выходного столбца — value .
• Если enable_ordinal имеет значение 1 , функция возвращает таблицу с двумя столбцами, включая ordinal столбец, состоящий из 1 значений индексов подстроок в исходной входной строке.

Аргумент enable_ordinal должен быть константным значением, а не столбцом или переменной. Он также должен быть битом или типом данных int со значением 0 или 1 . В противном случае функция вызывает ошибку.

Выходные строки могут быть расположены в любом порядке. Порядок не гарантирует соответствие порядка подстроек во входной строке. Можно переопределить окончательный ORDER BY порядок сортировки с помощью предложения инструкции SELECT , например ORDER BY value или ORDER BY ordinal .

0x0000 (char(0)) — это неопределенный символ в параметрах сортировки Windows и не может быть включен в STRING_SPLIT него.

Пустые строки нулевой длины присутствуют в том случае, если входная строка содержит два или несколько последовательных вхождений знака разделителя. Пустые подстроки обрабатываются так же, как и обычные подстроки. Вы можете отфильтровать все строки, содержащие пустую подстроку, с помощью WHERE предложения, например WHERE value <> '' . Если входная строка имеет значение NULL , STRING_SPLIT функция с табличным значением возвращает пустую таблицу.

Например, в следующей SELECT инструкции в качестве разделителя используется символ пробела:

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' '); 

При выполнении практики предыдущий SELECT возвращает следующую таблицу результатов:

значение
Lorem
ipsum
dolor
sit
amet.

Следующий пример включает ordinal столбец, передав 1 необязательный третий аргумент:

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1); 

Затем эта инструкция возвращает следующую результирующую таблицу:

значение	ordinal
Lorem	1
ipsum	2
dolor	3
sit	4
amet.	5

Примеры

А. Разделение строки значений с разделителями-запятыми

Следующая инструкция анализирует разделенный запятыми список значений и возвращает все непустые токены:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike' SELECT value FROM STRING_SPLIT(@tags, ',') WHERE RTRIM(value) <> ''; 

STRING_SPLIT возвращает пустую строку, если нет ничего между разделителем. Условие RTRIM(value) <> '' удаляет пустые маркеры.

B. Разделение строки значений с разделителями-запятыми в столбце

Таблица Product содержит столбец с разделенным запятыми списком тегов, как показано в следующем примере:

ИД продукта	Имя.	Теги
1	Full-Finger Gloves	clothing,road,touring,bike
2	LL Headset	bike
3	HL Mountain Frame	bike,mountain

Следующий запрос преобразовывает каждый список тегов и соединяет его с исходной строкой:

SELECT ProductId, Name, value FROM Product CROSS APPLY STRING_SPLIT(Tags, ','); 

ИД продукта	Имя.	Значение
1	Full-Finger Gloves	clothing
1	Full-Finger Gloves	road
1	Full-Finger Gloves	touring
1	Full-Finger Gloves	bike
2	LL Headset	bike
3	HL Mountain Frame	bike
3	HL Mountain Frame	mountain

Порядок выходных данных может отличаться, так как порядок не гарантирует соответствие порядка подстроек во входной строке.

C. Объединение по значениям

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

SELECT value as tag, COUNT(*) AS [number_of_articles] FROM Product CROSS APPLY STRING_SPLIT(Tags, ',') GROUP BY value HAVING COUNT(*) > 2 ORDER BY COUNT(*) DESC; 

D. Поиск по значению тега

Разработчикам необходимо создать запросы для поиска статей по ключевым словам. Они могут использовать представленные ниже запросы.

Поиск продуктов с одним тегом (clothing):

SELECT ProductId, Name, Tags FROM Product WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ',')); 

Поиск продуктов с двумя тегами (clothing и road):

SELECT ProductId, Name, Tags FROM Product WHERE EXISTS (SELECT * FROM STRING_SPLIT(Tags, ',') WHERE value IN ('clothing', 'road')); 

Д. Поиск строк по списку значений

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

SELECT ProductId, Name, Tags FROM Product JOIN STRING_SPLIT('1,2,3',',') ON value = ProductId; 

Предыдущее STRING_SPLIT использование является заменой общего антипаттерна. Такой антипаттерн может включать создание динамической строки SQL на уровне приложения или в Transact-SQL. Или можно добиться антипаттерна с помощью LIKE оператора. См. следующую инструкцию: SELECT

SELECT ProductId, Name, Tags FROM Product WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%'; 

F. Поиск строк по порядковым значениям

Следующая инструкция позволяет найти все строки с четным значением индекса:

SELECT * FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1) WHERE ordinal % 2 = 0; 

Приведенная выше инструкция возвращает следующую таблицу:

значение	ordinal
Техас	2
Вашингтон	4
Колорадо	6

G. Упорядочение строк по порядковым номерам

Следующая инструкция возвращает разделенные значения подстрок входной строки и их порядковые значения, упорядоченные столбцу ordinal :

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC; 

Приведенная выше инструкция возвращает следующую таблицу:

значение	ordinal
А	5
Б	4
О	3
D	2
E	1

Связанный контент

• LEFT (Transact-SQL)
• LTRIM (Transact-SQL)
• RIGHT (Transact-SQL)
• RTRIM (Transact-SQL)
• SUBSTRING (Transact-SQL)
• TRIM (Transact-SQL)
• Строковые функции (Transact-SQL)

Обратная связь

Были ли сведения на этой странице полезными?

Обратная связь

Отправить и просмотреть отзыв по