CODEWITHSUNDEEP
phpmysqlsqlselectsql-order-by
pc_oc
pc_oc

Reputation: 545

ordering fields in a table upwards mysql

I have a column named user. And I have fields like this: user1, user2, ..., user10, user11, user12, user20. BUT fields not always begin by user

I want to sort these fields upwardly, ie, I'm doing the following query:

SELECT * FROM table ORDER BY user ASC

This query returns: user1, user10, user11, user12, user2, user20

And the sort I want is the following: user1, user2, user11, user12, user20

how can I do this sort? The programming language I'm using is php

Upvotes: 2

Views: 118

Answers (6)

fthiella
fthiella

Reputation: 49089

It is probably better to add another column with the position of the user, and then order by that column. But if you can't change your table structure, a query like this should return your rows in the correct order:

SELECT
  tablename.*
FROM
  tablename
ORDER BY
  SUBSTR(user,
  LEAST(
    CASE WHEN LOCATE('0', user)>0 THEN LOCATE('0', user) ELSE LENGTH(user) END,
    CASE WHEN LOCATE('1', user)>0 THEN LOCATE('1', user) ELSE LENGTH(user) END,
    CASE WHEN LOCATE('2', user)>0 THEN LOCATE('2', user) ELSE LENGTH(user) END,
    CASE WHEN LOCATE('3', user)>0 THEN LOCATE('3', user) ELSE LENGTH(user) END,
    CASE WHEN LOCATE('4', user)>0 THEN LOCATE('4', user) ELSE LENGTH(user) END,
    CASE WHEN LOCATE('5', user)>0 THEN LOCATE('5', user) ELSE LENGTH(user) END,
    CASE WHEN LOCATE('6', user)>0 THEN LOCATE('6', user) ELSE LENGTH(user) END,
    CASE WHEN LOCATE('7', user)>0 THEN LOCATE('7', user) ELSE LENGTH(user) END,
    CASE WHEN LOCATE('8', user)>0 THEN LOCATE('8', user) ELSE LENGTH(user) END,
    CASE WHEN LOCATE('9', user)>0 THEN LOCATE('9', user) ELSE LENGTH(user) END
    )) + 0

Please see fiddle here.

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

How about

SELECT * FROM test ORDER BY
   LPAD(lower(user), 10,0) ASC

Check here

http://sqlfiddle.com/#!2/e49f2/16

Upvotes: 0

naveen goyal
naveen goyal

Reputation: 4629

Try this use CAST

SELECT * FROM table ORDER BY CAST(SUBSTRING(user, 5) AS unsigned)

check sqlfiddle http://sqlfiddle.com/#!2/5f307c/1

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use Mysql's SUBSTRING_INDEX if data contains the word user with number

SELECT 
  * 
FROM
  TABLE 
ORDER BY SUBSTRING_INDEX(`user`, 'user', - 1) ASC

Upvotes: 0

AdrianBR
AdrianBR

Reputation: 2588

you need to extract the number with a function, and sort by that

SELECT * FROM table ORDER BY numeric(user) ASC

here's a quick one (modified from a normalization func)

CREATE FUNCTION `numeric`(`str` VARCHAR(255) )
    RETURNS varchar(255) CHARSET utf8
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret VARCHAR(255) DEFAULT ''; 
  DECLARE c VARCHAR(1);   
  SET len = CHAR_LENGTH( str ); 
  REPEAT 
    BEGIN 
      SET c = MID( str, i, 1 ); 
      IF c REGEXP '[[:digit:]]' THEN 
        SET ret=CONCAT(ret,c); 
      END IF; 
      SET i = i + 1; 
    END; 
  UNTIL i > len END REPEAT; 
  RETURN ret; 
END

Upvotes: 0

Aziz Shaikh
Aziz Shaikh

Reputation: 16534

You can use PHP's Natural Sort function natsort() — Sort an array using a "natural order" algorithm.

Upvotes: 1

Related Questions