Search for an email in an nvarchar column in SQL Server

I am looking to search for a particular email (e.g., [e-mail]) in an nvarchar column in SQL Server. It should have been as easy as –

Select * from Discussion where Comments like '[e-mail]%'

However the above query is not covering one case. If the result set contains a Comment like “Comment from a user with email [e-mail]”. In this case the above query will return both records containing ‘[e-mail]’ and ‘[e-mail]’.

I am looking for a sql query where the email address is matched exactly and the rest of text can be anything.

I am trying to search for an email in multiple tables. The column where we are searching for an email could be an xml, nvarchar(max) for comments and nvarchar(50) for just an email. We need to do perform another job on the record set.

Result set: If I am searching for ‘[e-mail]’, the result set should contain the below:

1. lorem ipsum [e-mail]
2. [e-mail] lorem ipsum
3. Lorem ipsum [e-mail] dolor sit amet

The result set should not contain something like:

1. [e-mail] dolor sit amet
2. Lorem ipsum [e-mail] dolor sit amet

Thanks in advance.

Answer

I came up with the below query.

declare @searchText nvarchar(50) = '[e-mail]'

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 (' ', '<', '>', '"', '''', ',', ';', '=', '(', ')', '*'))

I am looking for either an exact match or otherwise checking the previous and next characters to assess if the match is meeting the criteria.