How to handle special characters in SQL Server queries

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.