MySQL database alphanumeric mixed field sorting problem

Source: Internet
Author: User
Tags mixed

Sorts a character field in the MySQL data table, formatted as a single-digit + sequential number. The number does not have a leading zero and the length is not fixed.
This sequence of numbers with letters, sorted out, is not the same as the result we want, because it is not a pure number and can only be ranked by character rules.
The result is a1,a10,a11,a12 .... A19,a2,a20,a21 ....
Rather than the a1,a2,a3 that we usually feel, ... A10,A11, such a result.
There are two ways to get the right sort.
One is to change the structure of the field content, with zeros in front of the numbers, so that all field values have the same length.
The second is not to change the contents of the field, in the sort of SQL statement to find a way.

Order BY mid (column,2,10) +1

Mid () The first argument is a string content, which can be a field name. The second argument is the position of the starting character, and I need to start with the second character here. The third parameter is the length that needs to be obtained, to choose a value that can contain the length of all field content, rather than small.
But this is just a "string of digital content", sorting it still uses the character rules, will not get the results we want.
Then we need to add a value to this result, let MySQL do an arithmetic operation, and then get an integer value, then the order is the result of the number sequence.
For example:
SELECT * from Equipmentmes ORDER by MID (eqid,2,10) +1

MySQL database alphanumeric mixed field sorting problem

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.