List of Special Characters For SQL Like Clause

SQL Server - Sql query like operator with special characters

Try this simple way using regular expression as follows:

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

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

Special characters inside the value does not work with like operator while the same works with = operator in SQL Server

The chars [. ], % and _ have special meanings with the like operator.

You need to handle them differently if you are using the like operator.

Specifically, you need to escape them using a char you then specify in the escape clause:

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

More details are available in official documentation

Escaping special characters in a SQL LIKE statement using sql parameters

You have two options:

• enclose them in [ and ]. So:

  where pattern like '[%]'
  

  Looks for the percentage character.
  Full list of characters to escape - '_', '%', '[', ']' with corresponding replacements '[_]', '[%]', '[[]', '[]]'. Sample code can be found in Escaping the escape character does not work – SQL LIKE Operator

• use an escape character that is unlikely to be in the string, such as a backtick:

  where pattern like '`%' escape '`'
  

  (See the syntax on MSDN - LIKE (Transact-SQL).)

In both cases, I would suggest that you make the substitution in the application layer, but you can also do it in SQL if you really want:

where pattern like replace(@pattern, '%', '[%]')

And, giving the end-user access to wildcards may be a good thing in terms of the user interface.

---

Note: there are couple more special characters '-' and '^' in the LIKE query, but they don't need to be escaped if you are already escaping '[' and ']'.

LIKE operator with a bindvar pattern which works also for special characters

You would want to escape the literal % and _ with backslash. For example, in PHP we might try:

$pattern = "something _10%_ else";
$pattern = preg_replace("/([%_])/", "\\\\$1", $pattern);
echo $pattern;  // something \_10\%\_ else

MySQL LIKE Operator with Special Characters Confusion

Maybe this help you understand the usage of escape chars in mySQL

https://stackoverflow.com/a/27061961/634698

SQL escape special characters when using LIKE clause

The brackets [] in your query are expanded to [[][]] by your function. Brackets are used to define a character range/set and this way you specify a set of two empty sets. That won't match your string.

---

You can instead adopt a different approach.

If you can find a character that can act as an escape character, you can use it together with ESCAPE keyword in LIKE search.

I modified your function to use CHAR(10) as an escape character (as an example):

ALTER FUNCTION [dbo].[Escape_Special_Character]
( 
@Value nvarchar(max) 
) 
RETURNS nvarchar(max) AS
BEGIN
DECLARE @Result nvarchar(max)
SET @Result = REPLACE( @Value, '[', char(10) + '[' );
SET @Result = REPLACE( @Result, ']', char(10) + ']' );
SET @Result = REPLACE( @Result, '%', char(10) + '%' );
SET @Result = REPLACE( @Result, '*', char(10) + '*' );
SET @Result = REPLACE( @Result, '_', char(10) + '_' );
SET @Result = REPLACE( @Result, '^', char(10) + '^' );
RETURN @Result
END
GO

And then you can do the search like this:

declare @Table table
(
[Value] nvarchar(max)
)
insert into @Table
select
'course name ~!@#$%^&*()_+={}[]\|;'':"<>?,./{|}~ÇüéâäàåçêëèïîìÄÅÉæÆôöòûùÿÖÜ¢£¥áíóúñѺªº¿©¬½¼¡«»°±²³´µ¶·¸¹º»¼½¾'
select * from @Table
where [Value] like '%' + dbo.Escape_Special_Character('course name ~!@#$%^&*()_+={}[]') + '%' 
        ESCAPE char(10)

See more in "Pattern Matching with the ESCAPE Clause" section of this MSDN page.

---

Related Topics