由于MySQL8.0，您可以使用 REPLACE 以及 REGEXP_SUBSTR 至 ORDER BY 列：

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

添加 ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(column, ' ', 2), ' ', -1) AS UNSIGNED) 条款应该有帮助。
筑巢 SUBSTRING_INDEX 具有正确参数的函数可以拆分和提取字符串部分。在这种情况下，将提取“group”中第一个字符串部分之后的数字部分。
查询

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)

请参见演示https://www.db-fiddle.com/f/sgquetkadjihra3bg3myca/0