How can i sort SQL by number, length & letters?

Question

How should I sort my SELECT ?

EXAMPLE LIST

1, 2, 2A, 4, 10, 10A

SQL

$query = "SELECT * FROM table WHERE column1 = '$var' ORDER BY length(column2), column2";

If I ORDER BY length() like my example, 2A will end up in the bottom.
If I remove length(), 10 comes after 1.

How do I sort so it appears as above?

[Something very similar here, just reverse the String Number calculation](http://stackoverflow.com/questions/4939518/sorting-string-column-containing-numbers-in-sql). Biggest problem is, you don't know how long the number is — Draken, Sep 30 '16 at 09:52

score 0 · Accepted Answer · answered Sep 30 '16 at 10:23

0

Like this:

$query = "SELECT * FROM table WHERE column1 = '$var' ORDER BY CAST(column2 as UNSIGNED), column2";

answered Sep 30 '16 at 10:23

moni_dragu

1,163
9
16

Works like a charm. Thank you! – Björn C Sep 30 '16 at 13:45
I'm glad I could help. – moni_dragu Oct 01 '16 at 19:31

score 0 · Answer 2 · answered Sep 30 '16 at 10:28

0

order by hex is best way to do this:

$query = "SELECT *, cast(hex(column2 as unsigned) as l FROM table WHERE column1 = '$var' ORDER BY l";

answered Sep 30 '16 at 10:28

Raymond Cheng

2,425
21
34

score 0 · Answer 3 · edited May 23 '17 at 10:34

With a custom function such as alphas found here, you can order it by first the number portion of the value followed by the string portion of the value.

If you define and populate a table as such:

CREATE TABLE test (t VARCHAR(255));
INSERT INTO test VALUES
  ('10A'),
  ('2'),
  ('2A'),
  ('4'),
  ('10'),
  ('1');

Then create a custom function called alphas which extracts the string portion (no numbers):

DELIMITER |
DROP FUNCTION IF EXISTS alphas; 
CREATE FUNCTION alphas( str CHAR(32) ) RETURNS CHAR(16) 
BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret CHAR(32) DEFAULT ''; 
  DECLARE c CHAR(1); 
  SET len = CHAR_LENGTH( str ); 
  REPEAT 
    BEGIN 
      SET c = MID( str, i, 1 ); 
      IF c REGEXP '[[:alpha:]]' THEN 
        SET ret=CONCAT(ret,c); 
      END IF; 
      SET i = i + 1; 
    END; 
  UNTIL i > len END REPEAT; 
  RETURN ret; 
END | 
DELIMITER ;

Then you can do a ordered query like this:

SELECT t FROM test ORDER BY CAST(t AS UNSIGNED), alphas(t);

The CAST function converts strings like 10A to an unsigned number of 10.

How can i sort SQL by number, length & letters?

3 Answers3