CODEWITHSUNDEEP
mysqlsqldatabase
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Sorting Username

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

Upvotes: 6

Views: 145

Answers (4)

Ian P
Ian P

Reputation: 1724

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.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171491

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

Upvotes: 11

Madhivanan
Madhivanan

Reputation: 13700

If the phras bx is fixed,

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

Upvotes: 0

juergen d
juergen d

Reputation: 204854

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

Upvotes: 0

Related Questions