In SQL Server queries, you often encounter special characters, such as single quotes "'", and so on, which are the methods that SQL Server users should be aware of.
We all know that in SQL Server queries, single quotes "'" are special characters, so when SQL Server queries are converted to double single quotes "".
But this is just one of the special characters, and in the actual project, the following special characters are found for the like operation: Underscore "_", percent sign "%", square brackets "[]" and "^" of the caret.
Its use is as follows:
Underline: Used in place of an arbitrary character (equivalent to a regular expression). )
Percent: used in place of any number of arbitrary characters (equivalent to * in regular expressions)
Square brackets: For escaping (in fact, only the left bracket is used for escaping, the right parenthesis matches the nearest left parenthesis using the nearest precedence principle)
Caret: Used to exclude some characters from matching (this is the same as in regular expressions)
Here are some examples of matches, and it should be explained that only the like operation has these special characters, = operation is not.
A_b ... a[_]b%
A%b ... a[%]b%
A[b ... a[[]b%
A]b ... a]b%
A[]b ... a[[]]b%
A[^]b ... a[[][^]]b%
a[^^]b ... a[[][^][^]]b%
In the actual processing, for the = operation, we generally only need to replace this:
', '
For a like operation, the following substitutions are required (note that the order is also important)
[[[] (This must be the first replacement!!!)
%, [%] (here% means that the characters you want to match include the% instead of a wildcard that is specifically used for matching)
_, [_]
^-[^]
How to handle special characters in SQL Server queries