1. Origin of the problem

Filed +1, order by filed +1, or Order by CAST(server_id as SIGNED) desc are not acceptable because they are not pure numbers.


2

Table structure:



Default data:



2.1 Definition Specifications

After discussing with the customer (it is important to highlight communication), the customer said that the following number is three at most and two at least.

The data will only show up

Example 1: Xingcheng 10-1 V Example 2: Xingcheng 10-101-123 V

Counterexample 1: Xingcheng 10 X


Counterexample 2: Newtown 10-1-123-13x


2.2 Use mysql string splitting mechanism

Example data: Xingcheng 10-101-123

1. The location to get the first number is split into Xingcheng and 10-101-123

Use find_first_int(a custom function to find the position of the first number), and string interception

CREATE DEFINER = 'root'@'localhost' FUNCTION `find_first_int`(
        pData CHAR(60)
    )
    RETURNS int(11)
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ' '
BEGIN
  DECLARE vPos INT DEFAULT 1;
  DECLARE vRes INT DEFAULT 0;
  DECLARE vChar INT;
  WHILE vPos <= LENGTH(pData) DO
    SET vChar = ASCII(SUBSTR(pData,vPos,1));
    IF vChar BETWEEN 48 AND 57 THEN
      RETURN vPos;
    END IF;
    SET vPos = vPos + 1;
  END WHILE;
  RETURN NULL;
END;Copy the code
 select test,substring(test,find_first_int(testFrom 'testtable';Copy the code

    

2. The intercepted data are divided into three fields: one,tow and three. The corresponding onW of Xingcheng 10-101-123 = 10,two = 101 and three = 123

select test,
LEFT(substring(test,Locate('1'.test)  ),(LOCATE(The '-',substring(test,find_first_int(test)))-1)) one,

if(length(test)-length(replace(test.The '-'.' ')) = 1,
substring(substring(test,find_first_int(test)  ),(LOCATE(The '-',substring(test,find_first_int(test)))+1)) 
,
left(substring(substring(test,find_first_int(test)  ),(LOCATE(The '-',substring(test,find_first_int(test)))+1)),LOCATE( The '-',substring(substring(test,find_first_int(test)  ),(LOCATE(The '-',substring(test,find_first_int(test)))+1)))-1 )
)two,

right(substring(substring(test,find_first_int(test)  ),(LOCATE(The '-',substring(test,find_first_int(test)))+1)),LOCATE( The '-',substring(substring(test,find_first_int(test)  ),(LOCATE(The '-',substring(test,find_first_int(test)))+1)))-1 ) three

from `testtable`;

Copy the code

3. Order by one,two,three

select test
from `testtable`
order by 
LEFT(substring(test,find_first_int(test)  ),(LOCATE(The '-',substring(test,find_first_int(test))))) - 1 * 1,if(length(test)-length(replace(test.The '-'.' ')) = 1,
substring(substring(test,find_first_int(test)  ),(LOCATE(The '-',substring(test,find_first_int(test)))+1))*1 
,
left(substring(substring(test,find_first_int(test)  ),(LOCATE(The '-',substring(test,find_first_int(test)))+1)),LOCATE( The '-',substring(substring(test,find_first_int(test)  ),(LOCATE(The '-',substring(test,find_first_int(test)))+1)))-1 ))*1
,
right(substring(substring(test,find_first_int(test)  ),(LOCATE(The '-',substring(test,find_first_int(test)))+1)),LOCATE( The '-',substring(substring(test,find_first_int(test)  ),(LOCATE(The '-',substring(test,find_first_int(test)))) - 1) * 1 + 1));Copy the code

conclusion

There will be a lot of problems in the work, communication is very important, the second is to solve the problem.

And finally, I’m going to send a little wrapper around where I get one,two,thrre, because it looks a little bit messy 0, 0, so it’s better to wrap it up as a function.