Username Sort

Question

How to sort the username?

For example, I make this request:

SELECT * FROM `members` WHERE username LIKE 'bx%' ORDER BY username ASC 

Result

 bx1 bx10 bx11 bx12 bx13 bx14 bx15 bx16 bx17 bx18 bx19 bx2 bx20 bx21 bx3 bx4 bx5 

I want to return as follows:

 bx1 bx2 bx3 bx4 bx5 ... bx15 bx16 

etc.

Answer 1

 SELECT * FROM `members` WHERE username LIKE 'bx%' ORDER BY LENGTH(username), username 

The fact that you need to do this tells me that your circuit is denormalized. If possible, save the integer part of the username in a separate column if you need to perform operations on it.

SQL script example

Answer 2

You need to create a user-defined function that takes a string and returns an extended string. Assumptions: the string contains only one numeric component that is at the end and is smaller than the maximum integer size determined by the filling in fn. For example, ABC1234 → ABC00001234 ABC34 → ABC00000034 Follow these steps.

• Starting from the last character, Iterate through the character string transmitted by character (decreasing position) to the first numeric character. (C)
• get the length of the numerical part (4)
• add the plural "0" characters to the numeric part of the string (this determines the maximum size of the processed numeric part) (for example) 00001234
• The prefix is ​​not the numeric part of the string giving ABC00001234
• Exit

Sort by output You can also add a calculated field (which uses the function you just created) that returns this value and creates an index on it.

Answer 3

 SELECT * FROM `members` WHERE username LIKE 'bx%' ORDER BY cast(substring(username, 3) as unsigned) ASC 

Answer 4

If phras bx is fixed,

 SELECT * FROM `members` WHERE username LIKE 'bx%' ORDER BY replace(username,'BX','')*1 ASC