The relationship between Regex and like
In MySQL we often use regular expressions like filed '%?% '. But sometimes for regular filtering in complex scenarios, just a like looks a little bit out of hand.
The essence of the Regex is ', like ' percent ' = regex ', special words such as ^, $ can change the meaning of%.
- Like '%304% ' = Regex ' 304 '
- Like ' Zhang% ' = Regex ' ^ Zhang '
- Like '%03 ' = Regex ' 03$ '
- Like '%3% ' or like '%4% ' = Regex ' [34] ' a field contains 3 or contains 4
- Like '%3% ' or like '%4% ' = Regex ' 3|4 ' A field contains 3 or contains 4
# Example SQL---# ' matches all data containing ' 304 ' strings in the Fw_ver field: SELECT * from Tbl_upgrade_policy where fw_ver like '%304% '; select * from Tbl_upg Rade_policy where Fw_ver REGEXP ' 304 '; # ^ matches the starting position of the input string # Find the record of the operator field that has ' Zhang ' select * from Tbl_upgrade_policy where Operat Or like ' Zhang% '; select * from Tbl_upgrade_policy where operator regexp ' ^ Lee '; # $ matches the end of the input string # Find the record for the operator field that has ' 03 ' ended select * From Tbl_upgrade_policy where operator like '%03 ', select * from Tbl_upgrade_policy where operator regexp ' 03$ '; # [...] Character set Matches any one of the characters contained in it. # query out any number in 3 or 4 or 5 under the Update_type field select * from Tbl_upgrade_policy where update_type like '%3% ' or update_type like '%4% ' or Update_type like '%5% ', select * from Tbl_upgrade_policy where Update_type REGEXP ' [345] '; # P1|P2|P3 matches p1 or P2 or p3. SELECT * from Tbl_upgrade_policy where update_type like '%3% ' or update_type like '%4% ', select * from Tbl_upgrade_policy w Here update_type REGEXP ' 3|4 ', select * from Tbl_upgrade_policy where update_type like ' 3% ' or update_type like '%5 '; # Note: ' ^[3,5] ' matches with 3 or,Or 5 start of the record select * from Tbl_upgrade_policy where Update_type REGEXP ' ^[3,5]|4$ '; # ' ^3,5 ' matches records starting with 3, 5 select * from Tbl_upgra De_policy where Update_type REGEXP ' ^3,5|2$ ';
MySQL matches the regular match