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

by

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`, "https://stackoverflow.com/", 1) AS `first_part`,
         SUBSTRING_INDEX(`info`, "https://stackoverflow.com/", -1) AS `second_part`
FROM     `table`
ORDER BY `first_part` ASC,
         `second_part` ASC;

Result:

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`, "https://stackoverflow.com/", 1) AS `first_part`,
          CAST(SUBSTRING_INDEX(`info`, "https://stackoverflow.com/", -1) AS UNSIGNED) `second_part`
FROM     `table`
ORDER BY `first_part` ASC,
         `second_part` ASC;

More Related Contents:

  1. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE `id` [closed]
  2. Using PHP data in MySQL [closed]
  3. Checking the availability of rooms
  4. How to properly set up a PDO connection
  5. Commands out of sync; you can’t run this command now
  6. SELECT COUNT(*) AS count – How to use this count
  7. Get table column names in MySQL?
  8. How to resolve ambiguous column names when retrieving results?
  9. Loading .sql files from within PHP
  10. selecting unique values from a column
  11. Use bound parameter multiple times
  12. MySQL “NOT IN” query 3 tables
  13. Create a dynamic mysql query using php variables
  14. PHP: multiple SQL queries in one mysql_query statement
  15. How to separate DATE and TIME from DATETIME in MySQL?
  16. Why does this return Resource id #2? [duplicate]
  17. MySQL skipping first row
  18. How to reverse order output of a MySQL query
  19. Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘No index used in query/prepared statement’
  20. How to generate unique id in MySQL?
  21. MySql PHP select count of distinct values from comma separated data (tags)
  22. How to SELECT DEFAULT value of a field
  23. How would I implement a simple site search with php and mySQL?
  24. how do I get month from date in mysql
  25. Do I have to use mysql_real_escape_string if I bind parameters?
  26. SELECT row by DATEPART()
  27. Updating column so that it contains the row position
  28. PDO and MySQL ‘between’
  29. MySQL Join Multiple Rows as Columns
  30. Aggregated query without GROUP BY

Leave a Comment