MySQL Select a Piece of a String and Order by That Piece

MYSQL select a piece of a string and order by that piece

I would suggest that you look at the MySQL String Functions and more specifically the SUBSTRING_INDEX function. The reason I suggest this one over SUBSTRING is because the number before or after the slash might be more than a single number which would make the length of the first and/or second parts vary.

Example:

SELECT   `info`,
         SUBSTRING_INDEX(`info`, '/', 1) AS `first_part`,
         SUBSTRING_INDEX(`info`, '/', -1) AS `second_part`
FROM     `table`
ORDER BY `first_part` ASC,
         `second_part` ASC;

Result:

Additional Example

In this example, I'm using CAST to convert the second part into an unsigned integer just in case it contains additional characters such as symbols or letters. In other words, the second part of "web-4/15." would be "15" and the second part of "web-4/15****" would also be "15".

SELECT   `info`,
          SUBSTRING_INDEX(`info`, '/', 1) AS `first_part`,
          CAST(SUBSTRING_INDEX(`info`, '/', -1) AS UNSIGNED) `second_part`
FROM     `table`
ORDER BY `first_part` ASC,
         `second_part` ASC;

Mysql - order by certain strings

Use FIND_IN_SET()

SELECT * 
FROM Player 
ORDER BY find_in_set(position,'forward,midfielder,defender,goalkeeper')

MySql - order by string value first

In MySQL this works

SELECT foobar.description 
FROM foobar 
ORDER BY foobar.description <> 'fff',
         foobar.description ASC

But generally you can also use a case

SELECT foobar.description 
FROM foobar 
ORDER BY case when foobar.description = 'fff' then 1 else 2 end,
         foobar.description ASC

How to use substring in order by

Check out the SUBSTRING_INDEX() function:

select domainname from db.table
where criteria like ('*.com')
AND domainname like ('%.%.%')
ORDER BY SUBSTRING_INDEX(domainname, '.', -2);

Mysql Order By specific string

If you want the BB to appear at the beginning you can use:

select *
from yourtable
order by case when substring(name, 1, 2) = 'BB' then 0 else 1 end

See SQL Fiddle with Demo

If you want CD to appear second, then use:

select *
from yourtable
order by 
  case 
    when substring(name, 1, 2) = 'BB' then 0 
    when substring(name, 1, 2) = 'CD' then 1 
    else 2 end, name

See SQL Fiddle with Demo

Result for second query:

| ID |      NAME |
------------------
|  3 | BBAAAAAAA |
|  5 | BBAAAAAAA |
|  4 | CDAAAAAAA |
|  1 | AAAAAAAAA |
|  2 | ABAAAAAAA |

SQL Query, Sort on Substring

Try this:

For Question 1:

SELECT * FROM tableA
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(colName, ' ', 2), ' ', -1);

For Question 2:

SELECT * FROM tableA
ORDER BY SUBSTRING_INDEX(colName, ' ', -1);

MySQL order by string with numbers

You could use SUBSTR and CAST AS UNSIGNED/SIGNED within ORDER BY:

SELECT * FROM table_name ORDER BY
    SUBSTR(col_name FROM 1 FOR 1),
    CAST(SUBSTR(col_name FROM 2) AS UNSIGNED)

---

Related Topics