SQL like wildcard character fuzzy query techniques and special characters

Transfer from http://new-restart.iteye.com/blog/1416765

SQL like wildcard character fuzzy query techniques and special characters

How to deal with time query by 1,sql like

There are savetime (smalldatetime type) fields in the SQL data table with two records in the table with Savetime values: 2005-3-8 12:12:00 and 2005-6-6 14:02:02

I can't find anything with the following statement.
SELECT * from soft where soft.savetime like '%2005-3-8% '

The SQL help says:
"When searching for datetime values, it is recommended to use like, because datetime items may contain various date parts. For example, if you insert a value of 19981231 9:20 into a column named Arrival_time, the clause WHERE arrival_time = 9:20 will not find an exact match for the 9:20 string, because SQL Server converts it to 1900 years January 1 9:20. However, the clause WHERE arrival_time like '%9:20% ' will find a match. "

After running the following statement select Soft.*, CAST (soft.savetime as varchar) as Strdatetime,
Found
SQL smalldatetime the grid test into:
8 2005 12:12PM

What do I use like '%2005-3-8% ' to search all records for March 8, 2005?

SELECT *
From soft
where DateDiff (D,soft.savetime,convert (datetime, ' 20050308 ', 121)) =0

----
SELECT *
From soft
where convert (char (ten), soft.savetime,121) = ' 20050308 '

2, SQL special character handling method in the like operation:

In SQL Server queries, single quotes ' are special characters, so you need to convert them to double single quotes when querying.

In the like operation there are the following special characters: Underscore _, percent percent, square bracket [], 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)

_, [_]

^-[^]

3,sql like wildcard special usage: Escape

Explain the role of escape:

1. Define an escape character using the Escape keyword. In a pattern, the wildcard is interpreted as a normal character when the escape character is placed before the wildcard character. For example, to search for a string that contains the string 5% anywhere, use:
WHERE ColumnA like '%5/%% ' ESCAPE '/'
However, it seems that you cannot use "\" in MySQL.
2.ESCAPE ' Escape_character '
Allows you to search for wildcard characters in a string instead of using it as a wildcard. Escape_character is a character placed on a wildcard match either that represents this special purpose.

SELECT *
From finances
WHERE description like ' gs_ ' ESCAPE ' S '
GO

The meaning is:
For example, we want to search for a string "g_", if the direct like "G_", then the role of "_" is the wildcard character, rather than the characters, we will find such as "GA", "GB", "GC", and not the "g_" we need.
Use the like ' Gs_ ' ESCAPE ' s ' to denote special usage flags
3.create Table A (name varchar (10))
Go
Insert into a select ' 11%22 '
UNION ALL SELECT ' 11%33 '
UNION ALL SELECT ' 12%33 '
Go
SELECT * from a WHERE name like '%/%33 ' ESCAPE '/'--Specify the '/' symbol to indicate that the wildcard character following it is a "pop". (The second% is the character is not a wildcard)
Go
drop Table A

The result is:
Name
----------
11%33
12%33

Summarize:

%: matches 0 or more arbitrary characters; _: matches any single character; []: Matches a range; [^]: excludes a range

Symbol meaning
Like ' 5[%] ' 5%
Like ' [_]n ' _n
Like ' [A-CDF] ' A, B, C, D, or F
Like ' [-ACDF] '-, A, C, D, or F
Like ' [[] ' [
Like '] '
Like ' abc[_]d% ' abc_d and Abc_de
Like ' abc[def] ' abcd, ABCE, and ABCF
Like ' [^1-9] ' 0
Like ' [^1-9b-z] ' 0, a

For special characters appearing in strings: '% ', ' [', ' [] ', ' _ ' can be included with ' [] ', so that they are treated as ordinary characters in the pattern.

1. Use like ' [[] ' to match Special characters ' ['

Select 1 where ' [ABCDE ' like ' [[]% '
2. Match Special characters with like '] '

Select 1 where ']abcde ' like ']% '
3. Use like ' [[]] ' to match Special characters ' [] '

Select 1 where ' []abcde ' like ' [[]]%% '
4. Use like ' [_] ' to match Special characters ' _ '

Select 1 where ' _abcde ' like ' [_]% '
5. Match Special characters '% ' with like ' [%] '

Select 1 where ' abc%de ' like ' abc[%]de '
For other special characters: ' ^ ', '-', ' ' because they are used in ' [] ' themselves, they need to be escaped in a different way, so the escape clause in like is introduced, and it is also worth noting that escape can escape all special characters.

Select 1 where ' ^abcde ' like '!^abcde ' escape '! '
Select 1 where '-abcde ' like '!-abcde ' escape '! '
Select 1 where ']abcde ' like '!] ABCDE ' escape '! '

Select 1 where '%abcde ' like ' \%abcde ' escape ' \ '
Select 1 where '%abcde ' like '!%abcde ' escape '! '
Select 1 where '%abcde ' like ' #%abcde ' escape ' # '
Select 1 where '%abcde ' like ' @%abcde ' escape ' @ '

Select 1 where ' [ABCDE ' like '![ ABCDE ' escape '! '
Select 1 where ']abcde ' like '!] ABCDE ' escape '! '
The rule is to make escape characters with the characters immediately following the escape. The character following the escape is equivalent to the escape character in the C language string ' \ '.

Finally, look at a more complex match.

Select 1 where ' [^a-z]abcde ' like ' \[\^a\-z\]% ' escape ' \ '

SQL like wildcard character fuzzy query techniques and special characters