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.

Can you add some sample values for `title`? 2 or 3 examples should be enough. Please include at least one row that works, and another that does not. Questions like this are much easier to answer with sample data, for experimenting with. — David Rushton, Mar 24 '17 at 11:05

score 1 · Accepted Answer · answered Mar 24 '17 at 12:33

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.

score 0 · Answer 2 · answered Mar 24 '17 at 11:16

You might want to try this:

    declare @table table( title varchar(100))
    insert into @table values ('!@#$%^&*()-_=+[]{}\|;'':",./<>?')

    select  *
    from    @table
    where   title like '!@#$%^&%' escape '\'

    select  *
    from    @table
    where   title like '!@#$%^&*()-_=+\[]%' escape '\'

    select  *
    from    @table
    where   title like '!@#$%^&*()-_=+\[]{}\\|%' escape '\'

    select  *
    from    @table
    where   title like '!@#$%^&*()-_=+\[]{}\\|;'':",./<>?%' escape '\'

Make sure to escape the opening square bracket and the backslash. Also, there's no need to put the ^ character between brackets.

SQL Server - Sql query like operator with special characters

2 Answers2