Specific digits of the database field obtained or queried by MySQL
Mysql is often used to query specific fields and occasionally needs to display a part of the value of a specific field. How can this problem be solved?
Then there is a fuzzy query, if you need to match a certain score in the middle, this time like is very embarrassing, there will be some irrelevant records, how to deal with this?
1. Obtain specific digits:
1. Take the last three characters of the url Field
- Select SUBSTRING (url,-3) from link;
# This method can only be used for a fixed length. For example, the url contains a total of 8 characters, which can be written as follows:
- Select RIGHT ('url', length ('url')-5) from link;
2. 3rd bits from the left (including the third bits)
- Select SUBSTRING (url, 3) from link;
3. Take the three digits on the left
- Select SUBSTRING (url, 1, 3) from link;
# This method can only be used for a fixed length. For example, the url contains a total of 8 characters, which can be written as follows:
- Select LEFT ('url', length ('url')-5) from link;
4. Intercept (take 7 digits from 1st bits, for example, the sDate field value is)
- Select SUBSTRING (sDate, 1, 7) from forumdata;
The query result is 2013-06.
2. query specific digits:
1. Normal Fuzzy search
- Select * from cm_order where ordersn like '% 100 ';
2. A field has 13 BITs and the last four or five bits are 31.
- Select * from cm_order where SUBSTRING (ordersn, 9, 2) = 31;
- SELECT * from cm_order where RIGHT ('ordersn ', length ('ordersn')-8) like '123 ';
This type of fuzzy query will produce a lot of irrelevant data, but this type can only be used for fixed-length fuzzy queries, which is much more efficient than normal fuzzy queries.