MySQL sorting alphanumeric correctly

Question

My record id gives something like :

I can write query as

SELECT memberid 
FROM newmember_tbl 
ORDER BY memberid

Output:

 1. TNSFIT0002
 2. TNSFIT0003
 3. TNSFIT001
 4. TNSFIT0010 
 5. TNSFIT00103
 6. TNSFIT00104

in this TNSFIT00 is fixed. the remaining is string is i want to sorting in asceding.

I want to sort the following data items in the order they are presented below

 1. TNSFIT001
 2. TNSFIT0002
 3. TNSFIT0003
 4. TNSFIT0010
 5. TNSFIT00103
 6. TNSFIT00104

Have you considered including the query you are using? Try to show what you have done so far to resolve the question. — Steve E., Oct 19 '15 at 08:40
this will not help because you are looking for only 8 characters which is `TNSFIT00` .remove LEFT(memberid,8) , and try .visit here for more info http://www.w3resource.com/mysql/string-functions/mysql-left-function.php — Suchit kumar, Oct 19 '15 at 08:45
using ORDER BY memberid+0 it will shows as a TNSFIT0002 first, TNSFIT0003 second and then TNSFIT001 as they continue — Shilvant, Oct 19 '15 at 08:57
give a try to this:SELECT memberid FROM newmember_tbl ORDER BY memberid+00 ASC, memberid +0, memberid — Suchit kumar, Oct 19 '15 at 09:02
i can write SELECT memberid FROM memberid ORDER BY RIGHT(memberid, 3); the result display is correct. but i not confident to this query because when id becomes to more than 1000 members it is work properly or not? — Shilvant, Oct 19 '15 at 09:10
but in your case length will work fine:SELECT memberid FROM newmember_tbl ORDER BY LENGTH(memberid), memberid — Suchit kumar, Oct 19 '15 at 09:23
@Shilvant yes i checked it and you are right.i think it is not in mysql. — Suchit kumar, Oct 19 '15 at 09:24
Thanku u Very much sir! SELECT memberid FROM newmember_tbl ORDER BY LENGTH(memberid), memberid is work Very Well. So once again thanku @suchit Sir. — Shilvant, Oct 19 '15 at 09:25
This question might be possibly a duplicate of http://stackoverflow.com/q/153633/1025391 — moooeeeep, Oct 19 '15 at 09:36

score 0 · Answer 1 · answered Oct 19 '15 at 08:37

0

A simple order by on the column that contains the id will accomplish this.

SELECT * FROM your_table_name ORDER BY id ASC

answered Oct 19 '15 at 08:37

Amo

2,884
5
24
46

score 0 · Accepted Answer · answered Oct 19 '15 at 09:27

0

Try using length:

SELECT memberid FROM newmember_tbl ORDER BY LENGTH(memberid), memberid

answered Oct 19 '15 at 09:27

Suchit kumar

11,809
3
22
44

@Shilvant then you can click the check and mark it as an answer. – Suchit kumar Oct 19 '15 at 09:32

MySQL sorting alphanumeric correctly

2 Answers2