Microsoft SQL Server — фильтрация по 4 переменным
Вопрос задан
Изменён 11 месяцев назад
Просмотрен 143 раза
Как осуществлять фильтрацию по четырем переменным, при условии, что в некоторых может не быть значений (если переменная равна нулю, столбец должен отображать все данные)?
Строковой переменной «@lastName» можно присвоить ‘%’ и тогда столбец будет выводить все данные, а как быть с переменными Int?
Declare @statusNumbers Int, @departmentNumbers Int, @postNumber Int, @lastName Varchar(100) Set @statusNumbers = ? Set @departmentNumbers = ? Set @postNumber = ? Set @lastName = '%' Select Persons.Id, Persons.LastName, Persons.FirstName, Persons.SecondName, Statuses.Id, Statuses.Name as Status, Deps.Id, Deps.Name as Department, Posts.Id, Posts.Name as Post, Persons.DateEmploy as Employ, Persons.DateUnemploy as Unemploy From dbo.Persons Join dbo.Statuses on Statuses.Id = Persons.StatusId Join dbo.Deps on Deps.Id = Persons.DepId Join dbo.Posts on Posts.Id = Persons.PostId Where Persons.StatusId = @statusNumbers and Persons.DepId = @departmentNumbers and Persons.PostId = @postNumber and LastName Like '%' + RTRIM(@lastName) + '%'
Согласно комментарию и ссылки, нужно было:
Инициализировать переменные:
Declare @statusNumbers Int = null, @departmentNumbers Int = null, @postNumber Int = null, @lastName Varchar(100) = ‘%’
Изменить условие в ‘Where’:
Where (Persons.StatusId = @statusNumbers or @statusNumbers is null) and (Persons.DepId = @departmentNumbers or @departmentNumbers is null) and (Persons.PostId = @postNumber or @postNumber is null) and LastName Like ‘%’ + RTRIM(@lastName) + ‘%’
Проверив перебором переменных, все работает.
- sql
- sql-server
3
Простое(но не оптимальное) решение — это как в комментариях предложили:
(Persons.StatusId = @statusNumbers OR @statusNumbers IS NULL)
Но тогда фильтрация будет по всем указанным полям. А зачем искать по всем, если указано только одно(например)? Возникает ситуация, называемая «Kitchen Sink» — когда встанет вопрос оптимизации вашего запроса, то необходимо будет прибегнуть к динамическому SQL и сделать так(а лучше сразу):
DECLARE @statusNumbers INT = 1
DECLARE @departmentNumbers INT = NULL
DECLARE @postNumber INT = 3
DECLARE @lastName VARCHAR(100) = 'Zlobin'
DECLARE @script VARCHAR(MAX) = '
SELECT Persons.Id,
Persons.LastName,
Persons.FirstName,
Persons.SecondName,
Statuses.Id,
Statuses.Name AS Status,
Deps.Id,
Deps.Name AS Department,
Posts.
Id,
Posts.Name AS Post,
Persons.DateEmploy AS Employ,
Persons.DateUnemploy AS Unemploy
FROM dbo.Persons
JOIN dbo.Statuses ON Statuses.Id = Persons.StatusId
JOIN dbo.Deps ON Deps.Id = Persons.DepId
JOIN dbo.Posts ON Posts.Id = Persons.PostId
WHERE 1 = 1 '
IF @statusNumbers IS NOT NULL SET @script = CONCAT(@script, 'AND Persons.StatusId = ', @statusNumbers, ' ')
IF @departmentNumbers IS NOT NULL SET @script = CONCAT(@script, 'AND Persons.DepId = ', @departmentNumbers, ' ')
IF @postNumber IS NOT NULL SET @script = CONCAT(@script, 'AND Persons.PostId = ', @postNumber, ' ')
IF @lastName IS NOT NULL SET @script = CONCAT(@script, 'AND Persons.LastName LIKE ''%', TRIM(@lastName), '%'' ')
PRINT(@script) --Посмотреть текст запроса
EXEC(@script) --Выполнить текст запроса
1
Учитывая советы по «Kitchen Sink» и «sp_executesql», получается:
DECLARE @statusNumbers INT = NULL DECLARE @departmentNumbers INT = NULL DECLARE @postNumber INT = NULL DECLARE @lastName NVARCHAR(MAX) = NULL DECLARE @script NVARCHAR(MAX) = ' SELECT Persons.
Id,
Persons.LastName,
Persons.FirstName,
Persons.SecondName,
Statuses.Id,
Statuses.Name AS Status,
Deps.Id,
Deps.Name AS Department,
Posts.Id,
Posts.Name AS Post,
Persons.DateEmploy AS Employ,
Persons.DateUnemploy AS Unemploy
FROM dbo.Persons
JOIN dbo.Statuses ON Statuses.Id = Persons.StatusId
JOIN dbo.Deps ON Deps.Id = Persons.DepId
JOIN dbo.Posts ON Posts.Id = Persons.PostId
WHERE 1 = 1'
+ CASE WHEN @statusNumbers IS NOT NULL THEN ' AND Persons.StatusId = @statusNumbers' ELSE '' END
+ CASE WHEN @departmentNumbers IS NOT NULL THEN ' AND Persons.DepId = @departmentNumbers' ELSE '' END
+ CASE WHEN @postNumber IS NOT NULL THEN ' AND Persons.PostId = @postNumber' ELSE '' END
+ CASE WHEN @lastName IS NOT NULL THEN ' AND Persons.LastName LIKE @lastName' ELSE '' END
DECLARE @params NVARCHAR(MAX) = '
@statusNumbers INT,
@departmentNumbers INT,
@postNumber INT,
@lastName NVARCHAR(100)'
EXEC sp_executesql @script, @params,
@statusNumbers,
@departmentNumbers,
@postNumber,
@lastName
Только не знаю, есть ли разница между:
IF @statusNumbers IS NOT NULL SET @script = CONCAT(@script, 'AND Persons.
StatusId = ', @statusNumbers, ' ')
Или
+ CASE WHEN @statusNumbers IS NOT NULL THEN ' AND Persons.StatusId = @statusNumbers' ELSE '' END
0
Зарегистрируйтесь или войдите
Регистрация через Google
Регистрация через Facebook
Регистрация через почту
Отправить без регистрации
Почта
Необходима, но никому не показывается
Отправить без регистрации
Почта
Необходима, но никому не показывается
Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки
Поиск информации в тексте средствами MS SQL Server — Разработка на vc.
ruВ своей работе нам часто приходится анализировать текст, изложенный в свободной форме (назначения платежей, комментарии, адреса и др.) с целью вычленить определенную информацию. В excel для этого можно использовать текстовые формулы и регулярные выражения VBA. Кроме того, регулярные выражения и текстовые функции можно использовать в python и других языках программирования.
2818 просмотров
Но как быть, если объем информации измеряется миллионами записей в базе данных, а времени на выгрузку и анализ небольших порций данных нет? Использование Transact-SQL (который хоть и не поддерживает регулярные выражения в полной мере, имеет более расширенный функционал оператора like, чем стандартный SQL) позволит переложить вычисления на плечи СУБД и сэкономить время на копировании данных.
Для примера выгрузили отзывы клиентов со страницы www.banki.ru/services/responses/bank/sberbank/ и импортировали данные в MS SQL Server. Таблица banki_ru_messages содержит 3 поля: дату отзыва, заголовок и текст.
0-9]%’
and [Номер телефона] not like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’
)
if @t like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%’
begin
update [dbo].[banki_ru_messages]
set [Номер телефона]=right([Номер телефона], len([Номер телефона])-12)
where [Номер телефона] [email protected]
end
else
begin
update [dbo].[banki_ru_messages]
set [Номер телефона]=right([Номер телефона], len([Номер телефона])-1)
where [Номер телефона] [email protected]
end
end
go
update [dbo].[banki_ru_messages]
set [Номер телефона]=left([Номер телефона], 11)
where [Номер телефона] is not null
go
alter table [dbo].[banki_ru_messages]
alter column [Номер телефона] nvarchar(11)
go
select * from [dbo].[banki_ru_messages]
where [Номер телефона] is not null
Аналогичным способом, изменив количество цифр, можно искать номера счетов, ИНН, почтовые индексы.
Для поиска адресов электронной почты можно воспользоваться следующим кодом:
alter table [dbo].
[banki_ru_messages]
add [E-mail] nvarchar(max)
go
update [dbo].[banki_ru_messages]
set [E-mail]=[Сообщение]
where [Сообщение] like ‘%[A-z0-9]@[A-z0-9]%’
go
declare @t nvarchar(max)
while exists (select top 1 [E-mail] from [dbo].[banki_ru_messages]
where [E-mail] is not null and charindex(‘ ‘, [E-mail])>0)
begin
set @t=(select top 1 [E-mail] from [dbo].[banki_ru_messages]
where [E-mail] is not null and charindex(‘ ‘, [E-mail])>0)
if left(@t, charindex(‘ ‘, @t)-1) like ‘%[A-z0-9]@[A-z0-9]%’
begin
update [dbo].[banki_ru_messages]
set [E-mail]=left(@t, charindex(‘ ‘, @t)-1)
where [E-mail][email protected]
end
else
begin
update [dbo].[banki_ru_messages]
set [E-mail]=right(@t, len(@t)-charindex(‘ ‘, @t)+1)
where [E-mail][email protected]
end
end
go
select * from [dbo].[banki_ru_messages]
where [E-mail] is not null or [ИНН] is not null or [Номер телефона] is not null
Описанные алгоритмы конечно же имеют погрешность (количество подряд идущих цифр не всегда однозначно определяет их суть, да и символ «@» встречается не только в адресах электронной почты), но в определенных ситуациях позволяют сэкономить время и дать представление о наличии и объеме искомой информации в текстовых полях.
Оператор SQL Server LIKE с примерами — Учебное пособие по SQL Server
Цель обучения
Цель этого учебного пособия по SQL Server — научить вас использовать логический оператор LIKE для сопоставления с шаблоном.
Что такое оператор
LIKE в SQL Server? Оператор SQL Server LIKE выполняет гибкое сопоставление шаблонов с помощью подстановочных знаков, что устраняет необходимость указывать точный шаблон или весь шаблон. Его можно использовать в SELECT , UPDATE или DELETE запрос и является альтернативой IN, = и !=.
Операция
Оператор SQL Server LIKE следует за предложением WHERE в запросе SQL и возвращает те строки в наборе результатов, которые соответствуют указанному шаблону. Также можно свести на нет действие оператора НРАВИТСЯ, добавив НЕ (что делает его НЕ НРАВИТСЯ), чтобы запрос возвращал набор результатов, который не соответствует указанному шаблону.
Различные подстановочные знаки, которые можно использовать с оператором LIKE, обсуждаются ниже.
- % (Процент) — Процент используется для соответствия одному или нескольким символам в шаблоне и может быть указан в начале, конце или в середине шаблона для сопоставления.
- _ (Подчеркивание) — Подчеркивание используется для сопоставления одного символа в шаблоне и может быть указано в начале, конце или в середине шаблона для сопоставления.
- [] Скобка — Скобка используется для сопоставления одного символа из списка или диапазона символов и может быть указана в начале, конце или середине шаблона для сопоставления. 9] Caret — После знака вставки в квадратных скобках следует список символов или диапазон символов для отрицательного соответствия одному символу в шаблоне, т. е. для проверки и гарантии того, что оцениваемый шаблон не содержит этот символ (символы) в указанной позиции.
- (!) Восклицательный знак — Восклицательный знак используется в качестве escape-символа для соответствия символам, которые обычно используются в качестве подстановочных знаков (т. е. всем упомянутым выше символам) в запросе LIKE. Символ ESCAPE указывает оператору LIKE рассматривать любой подстановочный знак, указанный после escape-символа, как обычный обычный символ в шаблоне.
е. всем упомянутым выше символам) в запросе LIKE. Символ ESCAPE указывает оператору LIKE рассматривать любой подстановочный знак, указанный после escape-символа, как обычный обычный символ в шаблоне.SQL Server
Оператор LIKE СинтаксисОсновной синтаксис оператора LIKE в SQL Server следующий.
SELECT выражения ИЗ столов ГДЕ выражение LIKE | НЕ НРАВИТСЯ [шаблон [ESCAPE escape_character]];
В этом синтаксисе
- выражения — выражения, определяющие здесь столбцы или вычисления, которые вы хотите получить. Если вы хотите получить все столбцы, просто используйте * вместо выражений.
- таблицы — одна или несколько таблиц, из которых вы хотите получить данные.
- ГДЕ условия — Необязательно. Это используется для указания некоторых условий при выборе данных. Если вы не используете предложение WHERE, будут выбраны все доступные строки.
- LIKE – Логический оператор, используемый для сопоставления со строковым шаблоном.
- NOT LIKE — логический оператор, используемый для отрицательного соответствия шаблону строки.
- шаблон – Строка, состоящая из алфавита, цифры, специального символа (включая подстановочные знаки) или одного или всех из них.
- ВЫХОД – Дополнительно. Ключевое слово, используемое для указания символа, используемого в качестве escape-символа.
- escape-символ — необязательно. Символ, используемый в качестве escape-символа. Обычно !.
Оператор SQL Server LIKE Примеры
ПРИМЕЧАНИЕ. Столбцы также называются полями или атрибутами, и эти термины взаимозаменяемы.
Давайте посмотрим, как это работает со всеми подстановочными знаками и типами запросов. Предположим, у нас есть таблица клиентов с приведенными ниже данными. Мы запросим его, используя LIKE, чтобы продемонстрировать различные сценарии использования.
| customer_id | first_name | last_name | customer_email | customer_comment | |
| 1 | Alicia | Keys | alicia_alicia@gmail. com | Я хочу скидку 15% на следующую покупку | |
| 2 | Инди | Росси | [email protected] | ||
| 3 | Джек | Смит | [email protected] | Есть ли распродажа на следующей неделе? Какая % скидка? | |
| 4 | Пит | Уильямс | [email protected] | @help, служба поддержки | |
| 5 | Casey | Kugelman | [email protected] | @help, please call me back | |
| 6 | Lauren | Crow | [email protected] | Where новые обновления продукта? | |
| 7 | Стивен | Флеминг | [email protected] | NO Comments.0113 [email protected] | Требуется обратный звонок и возврат 15% |
1) SQL Server
LIKE – % (Letpercentage) 4 подстановочных знака, см.
как мы можем использовать символ % в разных позициях в шаблоне для сопоставления нескольких символов с использованием таблицы клиентов.a) Проценты в конце примера
Приведенный ниже запрос является примером использования % для перечисления комментариев клиентов из таблицы клиентов, которые начинаются с буквы «w».
ВЫБОР * ОТ клиентов ГДЕ customer_comment НРАВИТСЯ 'w%';
Запрос выдаст следующий результат.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 2 | Indi | Rossi | [email protected] | When is следующая большая распродажа? |
| 6 | Lauren | Crow | [email protected] | Где новые обновления продуктов? |
b) Проценты в начале примера
Приведенный ниже запрос является примером использования % для перечисления клиентов, у которых есть идентификаторы электронной почты в gmail, т.
е. идентификаторы электронной почты, которые заканчиваются на «gmail.com».
ВЫБЕРИТЕ * ОТ клиентов ГДЕ customer_email НРАВИТСЯ '%@gmail.com';
Запрос выдаст следующий результат.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 1 | Alicia | Keys | [email protected] | I want a 15% скидка на следующую покупку |
| 2 | Инди | Росси | [email protected] | Когда следующая большая распродажа? |
| 6 | Лорен | Ворона | [email protected] | Где новые обновления продуктов? |
| 7 | Стивен | FLEMING | [email protected] | Нет комментариев .. |
C).
Пользовательский пример. идентификаторы электронной почты, в которых есть «co.».
ВЫБЕРИТЕ * ОТ клиентов ГДЕ customer_email НРАВИТСЯ '%co.%';
Запрос выдаст следующий результат.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 4 | Pete | Williams | [email protected] | @help, служба поддержки |
| 5 | Кейси | Кугельман | [email protected] | @help, перезвоните мне |
2) SQL Server
LIKE – _ (подчеркивание) примеры подстановочных знаковразличные позиции в шаблоне для соответствия одному символу с использованием таблицы клиентов.
Приведенный ниже запрос представляет собой пример, в котором _ используется для вывода имен клиентов, в которых вторым символом является «а».
ВЫБЕРИТЕ * ОТ клиентов ГДЕ first_name LIKE '_a%';
Приведенный выше запрос выдаст следующий результат.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 3 | Jack | Smith | [email protected] | Есть ли распродажа на следующей неделе? Какая % скидка? |
| 5 | Кейси | Кугельман | [email protected] | @help, пожалуйста, перезвоните мне |
| 6 | Lauren | Crow | [email protected] | |
| 8 | Vanessa | май | [email protected] | Нужный обратный звонок и возврат 15% |
3) Sql Server 9000.1013 — 9013 — 9013 — 9013 — 9013 — 9013 — 9013 — 9013 — 9013 — 9013 — 9066 — 9013 — 9013 — 9013 — 9013 — 9013 — 9013 — 9013 — 9013 —
— 9013 — — 9013 — — —.
Давайте посмотрим, как мы можем использовать символ [] в разных позициях в шаблоне, чтобы сопоставить символ из списка или диапазона, используя таблицу клиентов.
a) Пример списка символов
Приведенный ниже запрос представляет собой пример, в котором используется [] для перечисления имен клиентов, начинающихся с «a», «c» или «l», как указано в списке символов в квадратных скобках.
ВЫБЕРИТЕ * ОТ клиентов ГДЕ first_name LIKE '[acl]%';
Приведенный выше запрос выдаст следующий результат.
| customer_id | first_name | last_name | customer_email | customer_comment |
| 1 | Alicia | Keys | [email protected] | I want a 15% discount on the next purchase |
| 5 | Casey | Kugelman | ckugel1999 @yahoo. co.in | @help, перезвоните мне |
| 6 | Лорен | Кроу | [email protected] | Где новые обновления продукта? |
b) Пример диапазона символов
Приведенный ниже запрос является примером использования [] для вывода имен клиентов, начинающихся с любого символа в указанном диапазоне от a до l (который включает 12 символов алфавита).
ВЫБЕРИТЕ * ОТ клиентов ГДЕ first_name LIKE '[a-l]%';
Приведенный выше запрос выдаст следующий результат.
| идентификатор_клиента | имя_имя | last_name | customer_email | customer_comment | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1 | Alicia | Keys | [email protected] | I want a 15% dicounnt on the next purchase | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 2 | Инди | Росси | indi4u@gmail. com | Когда следующая большая распродажа? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 3 | Джек | Смит | [email protected] 9аккл]%’; Приведенный выше запрос выдаст следующий результат.
7). 0006 — пример запроса UPDATEДавайте рассмотрим один пример того, как мы можем использовать LIKE в запросе на обновление для изменения записей, которые соответствуют (или не соответствуют, если мы используем NOT LIKE) шаблону, указанному с помощью LIKE. Приведенный ниже запрос является примером удаления комментариев клиентов, имеющих идентификаторы электронной почты Yahoo. ОБНОВЛЕНИЕ клиентов УСТАНОВИТЬ клиент_комментарий = NULL ГДЕ customer_email как '%yahoo%'; Мы можем проверить обновление, запустив ниже запрос SELECT, который отобразит обновление. ВЫБОР * ОТ клиентов ГДЕ customer_email как '%yahoo%';
8) SQL Server LIKE — пример запроса DELETEДавайте рассмотрим один пример того, как мы можем использовать LIKE в запросе DELETE для удаления совпадающих записей (или не соответствует, если мы используем NOT LIKE) шаблон, указанный с помощью LIKE. Приведенный ниже запрос является примером удаления записей о клиентах из таблицы клиентов, у которых есть идентификаторы электронной почты Yahoo. УДАЛИТЬ ОТ клиентов ГДЕ customer_email как '%yahoo%'; Мы можем проверить обновление, выполнив приведенный ниже запрос SELECT, который показывает только 6 записей после удаления 2. SELECT * FROM customers;
|
9, [ и ].
co.in
com
В таких инструментах, как SSMS, шаблоны регулярных выражений можно указать в параметрах «Найти что» и «Найти и заменить».
Например, товары, начинающиеся с символов между L – P: