How to order by a part of a column ?

Question

I want to make an order by stuff using a part of some column.For example,some records of the column user look as below

vip1,vip2,vip21,vip10,vip100

If I do a order by user,the order would be

vip1,vip10,vip100,vip2,vip21

Then how can I make the result as follows

vip1,vip2,vip10,vip21,vip100

Thanks.

Answer 1

What RDBMS?

For SQL Server to get numeric sorting rather than lexicographic sorting it would be something like

ORDER BY CAST(RIGHT(col, LEN(col)-3) AS INT)

For MySQL

ORDER BY CAST(RIGHT(col, LENGTH(col)-3) AS UNSIGNED)

but why are you storing the vip part at all if it is the same for all rows?

Answer 2

You can also replace the user name ('vip') with nothing ('') and add zero, then sort. Not any more efficient, just more generic.

Answer 3

Is the "prefix" part always exactly 3 characters? If not, this gets complicated. Umm, with Postgres you could

order by regexp_matches(userid,"^[a-z]*")[1], substring(userid, regexp_matches(userid,"^[a-z]*")[1])::int

I think that would work,I haven't tried it. Anyway, the point is, if you have some function that will do regular expressions, you couild pull off a leading string of alphas, then peal off what's left and convert it to int.

If you're really embedding a number in an alpha field, a better alternative is: Don't do that. If this is two different logical data items, then make it two fields. It's a lot easier to put fields together than to take them apart.