SQL like wildcard character fuzzy query techniques and special characters

1. SQL like method for time query processing

    the SQL data table has a savetime (smalldatetime type) field with two records in the table with Savetime value: 2005-3-8 12:12:00 and 2005-6-6 14:02:02 

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

     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 January 1900 1st 9:20. However, the clause WHERE arrival_timelike '%9:20% ' will find a match. " 

     Run the following statement after Select Soft.*,cast (soft.savetime as varchar) as Strdatetime, Find SQL to turn smalldatetime into: 038 2005 12:12pm 

     How do I use like '%2005-3-8% ' Search all records for March 8, 2005?

SELECT * FROM Soft Wheredatediff (D,soft.savetime,convert (datetime, ' 20050308 ', 121)) =0
----
SELECT * from soft where convert (char (ten), soft.savetime,121) = ' 20050308 '

2. How SQL handles special characters in a 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 to replace!!)

%->[%] (here% means that the characters you want to match include the% instead of the 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 wildcards in a string instead of using it as a wildcard character. Escape_character is a character placed on a wildcard match either that represents this special purpose.

SELECT * from Financeswhere description like ' gs_ ' ESCAPE ' S '


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 ( )  
    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 with '/' Symbol to describe the wildcard character following it. (The second% is a character that is not a wildcard)  
    go 
    drop   table   A

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

Summary:

%: 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 after 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