SQL regular expressions and regular expressions used in mybatis

This article mainly introduces SQL regular expressions and the method of using regular expressions in mybatis. It is very good and has certain reference value. Friends in need can refer to the pattern matching provided by

mysql The other type is using extended regular expressions.

When you test for matches on such patterns, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

Some characters that extend regular expressions are:

"." matches any single character.
A character class "[...]" matches any character within square brackets. For example, "[abc]" matches "a", "b", or "c". To name a range of characters, use a "-". "[a-z]" matches any lowercase letter, while "[0-9]" matches any number.
" * " matches zero or more of the things preceding it. For example, "x*" matches any number of "x" characters, "[0-9]*" matches any number of digits, and ".*" matches any number of anything.

Regular expressions are case-sensitive, but if you wish, you can use a character class to match both writings. For example, "[aA]" matches a lowercase or uppercase "a" and "[a-zA-Z]" matches any letter written either way.

Patterns match if it appears anywhere in the value being tested (SQL patterns match as long as they match the entire value).

To position a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning of the pattern or "$" at the end of the pattern.

To illustrate how extended regular expressions work, the LIKE query shown above is rewritten below using REGEXP:

To find names starting with "b" , use "^" to match the beginning of a name and "[bB]" to match a lowercase or uppercase "b":

mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";

My own use of regular expressions in Myabtis Formula

<select id="provinceLists" resultMap="BaseCountry"
 parameterType="java.lang.String">
 select
 code,label
 from institution
 where admlvl = &#39;2&#39; and
 code REGEXP "[0-9]*\.[0-9]*"
 </select>
 <select id="cityLists" resultMap="BaseCountry" parameterType="java.lang.String">
 select
 code,label
 from institution
 where admlvl = &#39;3&#39; and code REGEXP "[0-9]*\.[0-9]*\.[0-9]*"
 </select>
 <select id="countyLists" resultMap="BaseCountry" parameterType="java.lang.String">
 select
 code,label
 from institution
 where admlvl = &#39;4&#39; and code REGEXP "[0-9]*\.[0-9]*\.[0-9]*\.[0-9]*";
 </select>

The above is the detailed content of SQL regular expressions and regular expressions used in mybatis. For more information, please follow other related articles on the PHP Chinese website!