CODEWITHSUNDEEP
phpmysqlsortingnumeric
Homer_J
Homer_J

Reputation: 3323

PHP MySQL - Order Alpha and Numeric strings

I have the following data:

GROUP 1 - FINANCE
GROUP 10 - SALES
GROUP 11 - MARKETING
GROUP 12 - HR
GROUP 2 - OPS

When ORDERING the data in a MySQL question it, rightly, brings this data back in the above order. However, is there anyway (without changing the data you can order it in Numeric order? As follows

GROUP 1 - FINANCE
GROUP 2 - OPS
GROUP 10 - SALES
GROUP 11 - MARKETING
GROUP 12 - HR

Thanks!

Upvotes: 0

Views: 50

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

Since MySQL 8.0 you can use REPLACE and REGEXP_SUBSTR to ORDER BY the column:

SELECT * 
FROM table_name
ORDER BY CAST(TRIM(REPLACE(REGEXP_SUBSTR(column_name, '(GROUP )([0-9]+)'), 'GROUP', '')) AS UNSIGNED)

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

Adding ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(column, ' ', 2), ' ', -1) AS UNSIGNED) clause should help.

Nesting SUBSTRING_INDEX functions with the right parameters makes it possible to split and extract the string parts. it this case the numeric part after the first string part in this case 'GROUP' will be extracted.

Query

SELECT
 records.data
FROM (

SELECT 
 "GROUP 1 - FINANCE" AS data
UNION 
SELECT 
 "GROUP 10 - SALES" AS data
UNION 
SELECT 
 "GROUP 11 - MARKETING" AS data 
UNION 
SELECT 
 "GROUP 12 - HR" AS data  
UNION 
SELECT 
 "GROUP 2 - OPS" AS data   
) AS records
ORDER BY 
 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(records.data, ' ', 2), ' ', -1) AS UNSIGNED)

see demo https://www.db-fiddle.com/f/sGqUeTKaDjihRA3Bg3Myca/0

Upvotes: 1

Related Questions