0
select * 
from Table1 
where Email = 'test()<>[]:;@\\@test.com' 


select * 
from Table1 
where Email like '%test()<>[]:;@\\@test.com%'

The first query is returning data, while the second query is not. How can we use like in this query?

The second query should return the same data as the first.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amit Vasi
  • 1
  • 1
    [] is part of the LIKE pattern matching. which matches empty set of characters. To be able to use such characters, you have to ESCAPE them with a escape clause. Read more here https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver16 – siggemannen Mar 17 '23 at 11:46
  • 2
    Why do you want to use `LIKE` when `=` is returning the expected result? The LIKE predicate and the equals to operator are not interchangeable, they do different comparisons- – jarlh Mar 17 '23 at 11:49
  • What are you trying to do? This string isn't a valid email address nor can it be used to match email addresses – Panagiotis Kanavos Mar 17 '23 at 12:05
  • 1
    @PanagiotisKanavos, sometimes it's better not to know :P – siggemannen Mar 17 '23 at 12:32
  • 1
    Quite the opposite, the `why` is 120% of the answer. Especially in this case which makes little sense on its own – Panagiotis Kanavos Mar 17 '23 at 12:33

1 Answers1

2

As @siggemannen has rightly pointed out : (I will use his original sentence from above comment):

[] is part of the LIKE pattern matching. which matches empty set of characters. To be able to use such characters, you have to ESCAPE them with a escape clause.

So, Your query can be written as :

SELECT * FROM Table1 WHERE Email LIKE '%test()<>\[\]:;@\\\\@test.com%' ESCAPE '\';

Here is a demo using dbfiddle

Tushar
  • 3,527
  • 9
  • 27
  • 49