SQL learning using wildcard characters for data filtering

First, like operator

All of the operators described earlier are filtered for known values . Whether you match a value or multiple values, the test is greater than or less than the known value, or the value of a range is checked, the common denominator is that the values used in the filter are known.

But this method is not always easy to use. The following code is available:

Select  from T_check_infodetail

Requirement : Now we need to retrieve the line records containing "heart rate" in ItemName, this time using simple comparison operators must not be used, wildcard characters are required. With wildcards, you can create a search pattern that compares specific data, and here we can construct a wildcard search pattern if we want to find itemname that contain "heart rate" records:

Wildcard: A special character used to match a portion of a value.

Search mode: A search condition that consists of a literal value, a wildcard character, or a combination of both.

Using the wildcard search pattern, you must use the LIKE operator, like to indicate the DBMS (database management System), followed by a search pattern that uses wildcard matching instead of a simple equality match for comparison.

We have not completed the above search request above, and then complete the above retrieval task after the wildcard characters are described below.

Second, wildcard characters

1, percent semicolon (%) wildcard characters

This wildcard is the most commonly used wildcard character in our daily development, in the search string,% means any number of occurrences of any character, and now I use the "%" wildcard to complete the above requirements code as follows:

Select  from where  like ' Heart Rate% '

OK, complete the above requirements.

This code uses the search mode ' heart rate% ' and, when executing this sentence, will retrieve any itemname column values that begin with ' heart rate '. % tells the DBMS (database management System) to receive any character after ' heart rate ', regardless of how many characters he has.

Below we cover the requirements above, retrieve the ItemName column value contains the ' scope ' of the row data, this time with the above ' range% ' search mode can not search out, the following is the solution code:

Select  from where  like ' % range% '

The search mode '% range% ' indicates a value that matches any position containing the text ' range ', regardless of what character he or she appears before or after.

This represents the search for row data with a ' range ' in the ItemName column value, regardless of the character before or after it.

Next, change the above requirements again to retrieve the row data that begins with ' heart ' in the ItemName column value and ends with ' fixed '. Here is the workaround code:

Select  from where  like ' Heart% Set '

This code uses the search mode ' heart% ', in executing this clause, will retrieve any ItemName column value with ' Heart ' beginning, ' fixed ' the end of the row data,% tells the DBMS (database management System) to receive ' heart ' after the ' fixed ' any character, no matter how many characters in the middle.

When using the '% ' match, there are several points to note:

1,% can match almost anything, but with one exception, this is null.

2, pay attention to the search mode ' heart% ', found that there is a space behind, and this space just changed the original intention of our search, this time the DBMS back to match the ' fixed ' end of the row data, so when using wildcard characters to match the data, be sure to pay attention to the space.

2. The underscore (_) wildcard character

SQL learning using wildcard characters for data filtering