I want to sort a table by a column named 'generic_location'. generic location data is conformed by numbers, letters and then numbers like these: '4A1', '5AW89', '7AA89', ETC
I trying to split the data using regexp_substr and order by CAST(generic_location AS UNSIGNED) ASC but it doesn't show as I want. this is what I get.
- 4A14
- 4A15
- 4AW39
- 4AW70
- 4A75
- 4AW83
This is my current query:
SELECT gen_loc,
regexp_substr(generic_location, '^[0-9]*') AS `A`,
regexp_substr(generic_location, '[A-Z]+') AS `B`,
REGEXP_REPLACE(generic_location, '([0-9]+[A-Z]+)+', '') AS `C`
FROM boms ORDER BY CAST(A AS UNSIGNED) ASC, CAST(B AS UNSIGNED), CAST(C AS UNSIGNED);
I want the column order shows like:
- 4A14
- 4A15
- 4A75
- 4AW39
- 4AW70
- 4AW83
Is there any way to order the generic location column like this? Thank you!
