Поиск электронной почты в столбце nvarchar в SQL Server
Я ищу для поиска определенного адреса электронной почты (например, [email protected]) в столбце nvarchar в SQL Server. Это должно было быть так же просто, как -
Select * from Discussion where Comments like '%[email protected]%'
Однако приведенный выше запрос не охватывает один случай. Если набор результатов содержит комментарий типа «Комментарий от пользователя с адресом электронной почты [email protected]». В этом случае приведенный выше запрос вернет обе записи, содержащие «[email protected]» и «[email protected]».
Я ищу sql-запрос, в котором адрес электронной почты точно совпадает, а остальной текст может быть любым.
Я пытаюсь найти электронное письмо в нескольких таблицах. Столбец, в котором мы ищем электронную почту, может быть xml, nvarchar(max) для комментариев и nvarchar(50) только для электронной почты. Нам нужно выполнить еще одну работу с набором записей.
Набор результатов: Если я ищу «[email protected]», набор результатов должен содержать следующее:
- лорем ipsum [email protected]
- [email protected] лорем ipsum
- Lorem ipsum [email protected] dolor sit amet
Результирующий набор не должен содержать что-то вроде:
- [email protected] Долор Сит Амет
- Lorem ipsum [email protected] dolor sit amet
Заранее спасибо.
не могли бы вы уточнить, что именно вам нужно в результате?
Это бесплатный тип, или он всегда будет в формате 'Comment from a user with email {Email Address}', или вы могли бы что-то вроде «Пользователь с адресом электронной почты {Адрес электронной почты} оставил комментарий»? Есть ли текст после адреса электронной почты? В идеале, однако, похоже, что электронная почта должна быть другим столбцом, и вы должны исправить свой дизайн.
Кстати, если вы проверяете nvarchar, неплохо было бы сравнить и с nvarchar: N'%[email protected]%'
Я добавил еще несколько объяснений, чтобы помочь лучше понять проблему.
Тем не менее, вы не упомянули, какой результат вы ищете в приведенном выше запросе? например, [email protected], [email protected]?
@Dhrumilshah - я добавил несколько примеров того, как должен выглядеть набор результатов. Спасибо.
Пожалуйста, проверьте мой ответ, я пробовал со своей стороны и проверял, он работает с моей стороны
надеюсь, это поможет вам stackoverflow.com/a/65424138/9724371
Ответы 4
Возможно, было бы полезно проверить наличие не буквенно-цифрового символа после или перед электронной почтой (как описано здесь: LIKE Transact-SQL) и различить все возможные случаи:
Электронная почта находится посередине
Электронная почта находится в начале
Электронная почта находится в конце
Столбец содержит только адрес электронной почты
SELECT * FROM Discussion WHERE Comments LIKE '%[^a-z0-9][email protected][^a-z0-9]%' --CASE 1 OR Comments LIKE '[email protected][^a-z0-9]%' --CASE 2 OR Comments LIKE '%[^a-z0-9][email protected]' --CASE 3 OR Comments = '[email protected]' --CASE 4
Кроме того, таким образом вы можете охватить такие случаи, как «Lorem ipsum:[email protected]», которые вы, вероятно, хотели бы обнаружить, и это должно работать, как вы описали.
Если вы можете предположить, что электронное письмо находится в начале, конце или окружено пробелами, вы можете использовать шаблон и comments с пробелами:
where concat(' ', Comments, ' ') like '% [email protected] %'
Если могут быть другие разделители, такие как круглые скобки, синтаксис и т. д., то это сложнее. Один из способов — заменить их пробелами с помощью translate():
where concat(' ', translate(Comments, '(),:;', ' '), ' ') like '% [email protected] %'
К сожалению, . является частью шаблона, который вы ищете, поэтому его также сложно включить.
Прежде всего, нам нужно найти адрес электронной почты, содержащийся в тексте, поэтому для этого нам нужно идентифицировать «@», поэтому для этого нам нужно написать запрос, как показано ниже.
SELECT Id,Comments as Text,
CASE
WHEN CHARINDEX('@',Comments) = 0 THEN NULL
ELSE SUBSTRING(Comments,beginningOfEmail,endOfEmail-beginningOfEmail)
END email
INTO #Temp1 FROM Discussion
CROSS APPLY (SELECT CHARINDEX(' ',Comments + ' ',CHARINDEX('@',Comments ))) AS A(endOfEmail)
CROSS APPLY (SELECT DATALENGTH(Comments )/2 - CHARINDEX(' ',REVERSE(' ' + Comments),CHARINDEX('@',REVERSE(' ' + Comments ))) + 2) AS B(beginningOfEmail)
-- stored data in Temp table , you can use alternative
SELECT id,email FROM #Temp1
DROP table #Temp1
ВЫХОД
id email
1 [email protected]
2 [email protected]
3 [email protected]
4 [email protected]
5 [email protected]
6 [email protected]
Затем нам нужно сделать еще один фильтр в таблице #Temp нравиться
SELECT id,email FROM #Temp1
where LEFT(email, CHARINDEX('@', email + '@') -1) like 'fname'
ИЛИ
SELECT id,email FROM #Temp1
where LEFT(email, CHARINDEX('@', email + '@') -1) = 'fname'
ВЫХОД
id email
1 [email protected]
2 [email protected]
4 [email protected]
5 [email protected]
Вы можете скопировать и вставить этот запрос и проверить его работу с моей стороны,
Я придумал следующий запрос.
declare @searchText nvarchar(50) = '[email protected]'
select *
from Discussion
where
Comments = @searchText
or (
CHARINDEX(@searchText, Comments) > 0
and SUBSTRING(Comments, CHARINDEX(@searchText, Comments) - 1, 1) in (' ', '<', '>', '"', '''', ',', ';', '=', '(', ')', '*')
and SUBSTRING(Comments, CHARINDEX(@searchText, Comments) + LEN(@searchText), 1) in (' ', '<', '>', '"', '''', ',', ';', '=', '(', ')', '*'))
Я ищу либо точное совпадение, либо иным образом проверяю предыдущий и следующий символы, чтобы оценить, соответствует ли совпадение критериям.
Разве вы не можете просто добавить пробелы к почтовому адресу?
like '% [email protected] %'