Filtering by alphanumeric-CodePudding

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!

CodePudding user response：

The problem you are facing is casting varchar to int perhaps hoping to obtain ASCII code sum.

The correct query is the following where MySQL is using inbuilt alphabetical sorting


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, B, CAST(C AS UNSIGNED);

Notice B is not being cast.

Also, not sure why you had to use substrings, the following query achieves the same result


SELECT gen_loc, generic_location FROM boms ORDER BY generic_location ASC;