MySQL Sorting by substring of a field

Question

A very old DB couldn't be modified, and so instead of adding a "customType" field, the final user "invented" a way to categorize the items. In detail, if the description field:

starts with a * it's of type1
starts with a # it's of type2
starts with a ** it's of type3
otherwise, it's of type4

Now, I have been asked to generate some reports sorted by a "category" field but grouped by the type. Now, APART FROM SPLITTING THE QUERY IN 4 PARTS, and sure enough I can do it, I was wondering if I could do it in a single query.

If there would exist a "customType" field, the query would be stupid easy:

SELECT * FROM item ORDER BY customType, category

But unfortunately I cannot do it. My first try was something like

SELECT * FROM item ORDER BY SUBSTRING(description,2), category

But the problem is that I (correctly) get the items grouped by the type together, but the categories are not kept together. To be more clear, I would like to have an output like

aaaaa|cat1
ddddd|cat1
bbbbb|cat2
ccccc|cat2
#aaaa|cat5
#bbbb|cat5

But right now I am getting

aaaaa|cat1
bbbbb|cat2
ccccc|cat2
ddddd|cat1
#aaaa|cat5
#bbbb|cat5

As you can see, the cat1/cat2 are mixed up, the categories should stay together

Out of curiosity, what version of mysql is this and why can't you add the new column? — William Hammond
– William Hammond, Commented Oct 25, 2020 at 6:00
It's one of the latest MariaDB but the client running on Windows is already relying on those arbitrary types, and in theory I have no access to the source code of the client - because I should then implement the new field in there too — ZioBit
– ZioBit, Commented Oct 25, 2020 at 6:39

Tim Biegeleisen · Accepted Answer · 2020-10-25 05:57:55Z

2

You may try ordering by a CASE expression:

SELECT *
FROM item
ORDER BY
    CASE WHEN LEFT(description, 2) = '**' THEN 3
         WHEN LEFT(description, 1) = '*'  THEN 1
         WHEN LEFT(description, 1) = '#'  THEN 2
         ELSE 4 END,
    category;

Note that we check for descriptions starting with ** before checking for starting with *. This is to avoid misclassifying starts with ** in the same bucket as just starts with *.

answered Oct 25, 2020 at 5:57

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

ZioBit Over a year ago

I am such a newbie when it comes to MySQL that I didn't even ever SEE the "case when" statement, wow! That's a big one for me :) :) :) Let me try it immediately, thanks!

Collectives™ on Stack Overflow

MySQL Sorting by substring of a field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related