PHP MySQL - Order Alpha and Numeric strings

Question

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!

Is that column was numerical in the database it would do it automatically. Can you change the schema? Otherwise a simple cast to int before ordering would work. p.s I'm pretty sure this question has been answered many times on here already and a simple google would also sort it. — webnoob
– webnoob, Commented Jun 25, 2018 at 14:39
Here is an article that might be helpful. copterlabs.com/natural-sorting-in-mysql as @webnoob mentioned, you would be better off changing the schema, but if that's not possible, you would have a much easier time implementing a sort in php IMO (which is perfectly acceptable if the data set is small). — Bryan
– Bryan, Commented Jun 25, 2018 at 14:45
have you got any column which show it number? because if you want to sort by numbers you need numeric column. — Mahdi Aslami Khavari
– Mahdi Aslami Khavari, Commented Jun 25, 2018 at 14:45

Raymond Nijland · Accepted Answer · 2018-06-25 14:47:03Z

1

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

answered Jun 25, 2018 at 14:47

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sebastian Brosch · Accepted Answer · 2018-06-25 16:27:57Z

0

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)

edited Jun 25, 2018 at 16:27

answered Jun 25, 2018 at 14:46

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

1 Comment

Raymond Nijland Over a year ago

MySQL doesn't understand INT as a CAST type (you get a parse error) you need to use UNSIGNED instead. see demo db-fiddle.com/f/jkzdNKhdeTJbsko4GbkNAg/0

Collectives™ on Stack Overflow

PHP MySQL - Order Alpha and Numeric strings

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related