Sorting Username

Question

How to sort Username correctly?

For example I do this query:

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 like this:

bx1
bx2
bx3
bx4
bx5
...
bx15
bx16

and so on

Answer 1

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

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

SQL Fiddle Example

Answer 2

You need to create a user defined function which takes the string and returns an expanded string. The assumptions are: the string contains only one numeric component which is at the end and is less than the the maximum integer size determined by the padding in the fn Eg ABC1234 -> ABC00001234 ABC34 -> ABC00000034 Impliment the following steps

• Starting at the last character, Iterate through the passed string character by character (decreasing position) until the first non numeric character is found. (C)
• get the length of the numeric part (4)
• add a mutiple number of "0" characters to the numeric part of the string (this determins the max size of numeric part handled) giving (eg) 00001234
• Prefix by the non numeric part of the string giving ABC00001234
• Output

Sort by output You can also add a calculated field (which uses the function just created) which will give this value returned and create an index on it.