Hi I want to sort in MySQL a Varchar Field.

The content of that field is below:
HHD01
HHD100
HHD02
HHD37
SAD55

I tried using +0 at the end of the order by field but it does not work 🙁

How can i sort it properly Numerically like this

HHD01
HHD02
HHD37
HHD100
SAD55

Let me know
thanks

    One way would be to retrieve the results into an array in PHP and use [man]natsort/man to do the sorting.

      hi

      i can use natsort, but how can i just sort it descending and limit to only 1 value since i want to know the last value in order to increment the next.

        Try sorting on something like ...

        CONCAT(SUBSTRING(field, 1, 3), LPAD(SUBSTRING(field, 4), 3, '0'))

          I'd rather split it into one varchar and one int, since what you want to do is not sort numerically which doesn't make sense for letters, but rather sort letters alphabetically and numerals numerically. Also, this approach will save you the string manipulations.

            what if you do something like (based on Paulnaj's post):
            select *, SUBSTRING(field, 1, 3) AS left_part, SUBSTRING(field, 4) AS right_part ORDER BY right_part ASC, left_part ASC

            this would add 2 extra fields in the select (left_part, right_part) which you can then use to sort on. not sure if it would sort it as a numerical value though. Could be syntax error in the substring function I used, I don't know it from the back of my head and took it from our friend paulnaj here 🙂

              sorry guys does not work:

              select b.customerPurchaseCode from tblgoods a, tbl_customer b where a.customerid=b.customerid order by CONCAT(SUBSTRING(b.customerPurchaseCode, 1, 3), LPAD(SUBSTRING(b.customerPurchaseCode, 4), 3, '0')) desc limit 0,1

              It still shows not properly

              it comes as last used purchase code is:
              SAA99

              where as the last used purchase code is
              SAA155

                Hi,

                Just done a test and it works fine.

                I used this test data ...
                ADD20
                ADD2
                SHH10
                SHH11
                SHH1
                SHH10000
                SHH200
                SHH320
                SHH21
                AGG200
                AGG2
                AGG2005
                AGG35

                ... and this SQL ...

                SELECT CONCAT(SUBSTRING(`test_field`, 1, 3), LPAD(SUBSTRING(`test_field`, 4), 10, '0')) as `order_by`
FROM `test_table`
ORDER BY `order_by` DESC

                Only thing to note is that I increased the LPAD level to 10 zeroes to cover the larger numbers. Ended up with ...

                SHH0000010000
                SHH0000000320
                SHH0000000200
                SHH0000000021
                SHH0000000011
                SHH0000000010
                SHH0000000001
                AGG0000002005
                AGG0000000200
                AGG0000000035
                AGG0000000002
                ADD0000000020
                ADD0000000002

                Pluck off the top one with LIMIT 0,1 and you're done.

                Might not be the most efficient method, but it works.

                P.

                PS. If you are only interested in the numbers being in decreasing order, regardless of the 3-letter prefix, then use ...

                SELECT CONVERT(SUBSTRING(`test_field`, 4), UNSIGNED) as `order_by`
FROM `test_table`
ORDER BY `order_by` DESC

                  it works to some extent, i dont require padding, as it increases the SA01 to SA00001 which is incorrect SA01 shud remain SA01 and SA0001 should remain that way itself, else everything will go wrong.

                  Anyways thank a million for the help 🙂

                    I don't think you aren't getting it. The field I'm showing is just for the ORDER BY clause, not for use outside the query.

                    However, if you are making a distinction between SA1, SA01, SA001 (which is somewhat odd to say the least) then just change the LPAD character to something else, a hyphen say.

                      Write a Reply...