Vfrdtyky

I know this post is closed but I think my way could help some people. So there it is :

My dataset is very similar but is a bit more complex. It has numbers, alphanumeric data :

1

2

Chair 

3

0

4

5

-

Table

10

13

19

Windows

99

102

Dog

I would like to have the '-' symbol at first, then the numbers, then the text.

So I go like this :

SELECT name, (name = '-') boolDash, (name = '0') boolZero, (name+0 > 0) boolNum 

FROM table 

ORDER BY boolDash DESC, boolZero DESC, boolNum DESC, (name+0), name

The result should be something :

-

0    

1

2

3

4

5

10

13

99

102

Chair

Dog

Table

Windows

The whole idea is doing some simple check into the SELECT and sorting with the result.

Just do this:

SELECT * FROM table ORDER BY column `name`+0 ASC

Appending the +0 will mean that:

0,
10,
11,
2,
3,
4

becomes :

0,
2,
3,
4,
10,
11

I hate this, but this will work

order by lpad(name, 10, 0)  <-- assuming maximum string length is 10

                            <-- you can adjust to a bigger length if you want to

I had some good results with

SELECT alphanumeric, integer FROM sorting_test ORDER BY CAST(alphanumeric AS UNSIGNED), alphanumeric ASC

This type of question has been asked previously.

The type of sorting you are talking about is called "Natural Sorting".
The data on which you want to do sort is alphanumeric.
It would be better to create a new column for sorting.

For further help check
natural-sort-in-mysql

This should sort alphanumeric field like:
1/ Number only, order by 1,2,3,4,5,6,7,8,9,10,11 etc...
2/ Then field with text like: 1foo, 2bar, aaa11aa, aaa22aa, b5452 etc...

SELECT  MyField

FROM MyTable

order by 

    IF( MyField REGEXP '^-?[0-9]+$' = 0, 

    9999999999 ,  

    CAST(MyField AS DECIMAL) 

    ), MyField

The query check if the data is a number, if not put it to 9999999999 , then order first on this column, then order on data with text

Good luck!

SELECT
s.id, s.name, LENGTH(s.name) len, ASCII(s.name) ASCCCI
FROM table_name s
ORDER BY ASCCCI,len,NAME ASC;

Instead of trying to write some function and slow down the SELECT query, I thought of another way of doing this...

Create an extra field in your database that holds the result from the following Class and when you insert a new row, run the field value that will be naturally sorted through this class and save its result in the extra field. Then instead of sorting by your original field, sort by the extra field.

String nsFieldVal = new NaturalSortString(getFieldValue(), 4).toString()

The above means:

- Create a NaturalSortString for the String returned from getFieldValue()

- Allow up to 4 bytes to store each character or number (4 bytes = ffff = 65535)



| field(32)  |  nsfield(161)                            |   

  a1            300610001

String sortString = new NaturalSortString(getString(), 4).toString()

import StringUtils;



/**

 * Creates a string that allows natural sorting in a SQL database

 * eg, 0 1 1a 2 3 3a 10 100 a a1 a1a1 b

 */

public class NaturalSortString {



    private String inStr;

    private int byteSize;

    private StringBuilder out = new StringBuilder();



    /**

     * A byte stores the hex value (0 to f) of a letter or number.

     * Since a letter is two bytes, the minimum byteSize is 2.

     *

     * 2 bytes = 00 - ff  (max number is 255)

     * 3 bytes = 000 - fff (max number is 4095)

     * 4 bytes = 0000 - ffff (max number is 65535)

     *

     * For example:

     * dog123 = 64,6F,67,7B and thus byteSize >= 2.      

     * dog280 = 64,6F,67,118 and thus byteSize >= 3.

     *

     * For example:

     * The String, "There are 1000000 spots on a dalmatian" would require a byteSize that can 

     * store the number '1000000' which in hex is 'f4240' and thus the byteSize must be at least 5

     *

     * The dbColumn size to store the NaturalSortString is calculated as:

     * > originalStringColumnSize x byteSize + 1

     * The extra '1' is a marker for String type - Letter, Number, Symbol

     * Thus, if the originalStringColumn is varchar(32) and the byteSize is 5:

     * > NaturalSortStringColumnSize = 32 x 5 + 1 = varchar(161)

     *

     * The byteSize must be the same for all NaturalSortStrings created in the same table.

     * If you need to change the byteSize (for instance, to accommodate larger numbers), you will

     * need to recalculate the NaturalSortString for each existing row using the new byteSize.

     *

     * @param str        String to create a natural sort string from

     * @param byteSize   Per character storage byte size (minimum 2)

     * @throws Exception See the error description thrown

     */

    public NaturalSortString(String str, int byteSize) throws Exception {

        if (str == null || str.isEmpty()) return;

        this.inStr = str;

        this.byteSize = Math.max(2, byteSize);  // minimum of 2 bytes to hold a character

        setStringType();

        iterateString();

    }



    private void setStringType() {

        char firstchar = inStr.toLowerCase().subSequence(0, 1).charAt(0);

        if (Character.isLetter(firstchar))     // letters third

            out.append(3);

        else if (Character.isDigit(firstchar)) // numbers second

            out.append(2);

        else                                   // non-alphanumeric first

            out.append(1);

    }



    private void iterateString() throws Exception {

        StringBuilder n = new StringBuilder();

        for (char c : inStr.toLowerCase().toCharArray()) { // lowercase for CASE INSENSITIVE sorting

            if (Character.isDigit(c)) {

                // group numbers

                n.append(c);

                continue;

            }

            if (n.length() > 0) {

                addInteger(n.toString());

                n = new StringBuilder();

            }

            addCharacter(c);

        }

        if (n.length() > 0) {

            addInteger(n.toString());

        }

    }



    private void addInteger(String s) throws Exception {

        int i = Integer.parseInt(s);

        if (i >= (Math.pow(16, byteSize)))

            throw new Exception("naturalsort_bytesize_exceeded");

        out.append(StringUtils.padLeft(Integer.toHexString(i), byteSize));

    }



    private void addCharacter(char c) {

        //TODO: Add rest of accented characters

        if (c >= 224 && c <= 229) // set accented a to a

            c = 'a';

        else if (c >= 232 && c <= 235) // set accented e to e

            c = 'e';

        else if (c >= 236 && c <= 239) // set accented i to i

            c = 'i';

        else if (c >= 242 && c <= 246) // set accented o to o

            c = 'o';

        else if (c >= 249 && c <= 252) // set accented u to u

            c = 'u';

        else if (c >= 253 && c <= 255) // set accented y to y

            c = 'y';



        out.append(StringUtils.padLeft(Integer.toHexString(c), byteSize));

    }



    @Override

    public String toString() {

        return out.toString();

    }

}

For completeness, below is the StringUtils.padLeft method:

public static String padLeft(String s, int n) {

    if (n - s.length() == 0) return s;

    return String.format("%0" + (n - s.length()) + "d%s", 0, s);

}

The result should come out like the following

-1

-a

0

1

1.0

1.01

1.1.1

1a

1b

9

10

10a

10ab

11

12

12abcd

100

a

a1a1

a1a2

a-1

a-2

áviacion

b

c1

c2

c12

c100

d

d1.1.1

e

If you need to sort an alpha-numeric column that does not have any standard format whatsoever

SELECT * FROM table ORDER BY (name = '0') DESC, (name+0 > 0) DESC, name+0 ASC, name ASC

You can adapt this solutation to include support for non-alphanumeric characters if desired using additional logic.

This works for type of data:
Data1,
Data2, Data3 ......,Data21. Means "Data" String is common in all rows.

For ORDER BY ASC it will sort perfectly, For ORDER BY DESC not suitable.

SELECT * FROM table_name ORDER BY LENGTH(column_name), column_name ASC;