SQL Server - Sql query like operator with special characters

Question

We want to allow all below special characters in search query based on which results should be available to end user. so we enter all below values in one column itself and now want to run sql query with Like operator.

• !@#$%^&*()-_=+[]{}\|;':",./<>?

I looked for all possible solution like ESCAPE character, square bracket & so on. But it only works till

select * from table where title like '!@#$%[^]' ESCAPE '\'

As soon as i add '&', no rows are returned.

It seems i am missing something here.

Answer 1

Try this simple way using regular expression as follows:

SELECT * FROM <YourTable>
WHERE <YourColumn> LIKE '%[!@#$%^&*()-_=+{}\|;'':",./[<>?]%' OR <YourColumn> LIKE '%]%';

Note that ] had to be taken separately so that it doesn't end the regular expression.