Is it possible to sort a mysql table with regexp replace?

Question

Example table:

ID Name
1  Apple Color: Yellow
2  Apple Color: Red
3  Banana Color: Yellow
4  Banana Color: Brown
5  Apple Color: Yellow

Would it be possible to order this list by name, but only taking the fruit name into account, by removing the "Color: whatever" part? So the result would look like this?

ID Name
1  Apple
2  Apple
5  Apple
3  Banana
4  Banana

"Pseudocode" would be something like this i guess:

select * from fruits order by REGEXP_REPLACE(name, '/ Color:.*/', '') asc

because i eventually will be ordering the list after the total count of fruit. — Kristian Rafteseth, Jul 16 '14 at 23:21
even so, not sure why a regex order would be different from a regular order.. I'm curious as to what would cause an error in a count with a regular order by... if you could provide some data that would do that I would like to test it :) — John Ruddell, Jul 16 '14 at 23:23
cannot sort by name, because i will sort it after total count of fruit. so if the fruit "zzzuperfruit" have the most occurrences, obviously it will not order correctly, since a normal name sort will give for example apples first. thats why i need to strip the color part off the rows where theres a color. — Kristian Rafteseth, Jul 16 '14 at 23:41
@KristianRafteseth you should redo the structure of your table. see my post on a way to do that.. that will make your output much easier to handle and will make it more scalable in the future — John Ruddell, Jul 16 '14 at 23:50

score 3 · Answer 1 · edited Jul 16 '14 at 23:34

3

Use SUBSTRING_INDEX(str,delim,count)

... ORDER BY SUBSTRING_INDEX(str,delim,count)

Link: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

edited Jul 16 '14 at 23:34

Avinash Raj

172,303
28
230
274

answered Jul 16 '14 at 23:28

LLPrudente

119
3

score 0 · Answer 2 · edited May 23 '17 at 12:05

Short answer, no, not without a UDF - How to do a regular expression replace in MySQL?

As mentioned by @LLPrudente in his answer you can use SUBSTRING_INDEX if the field in question always has the colour after the colon and nothing else. Expanding his suggestion you could do the following

... ORDER BY TRIM(SUBSTRING_INDEX(name, ':', -1))

And finally a slightly better answer, really these should be stored as two seperate fields i.e. "Name" and "Color" instead of this. It may be you have sanitised data and so made it appear as if this data can be better presented, and chances are that this is the case somehow in your actual data set. Consider stepping back and rethinking your structure here as the above will not be the most efficient way to go about things.

John Ruddell · Answer 3 · 2014-07-17T00:34:53.550

Here is a semi simplistic way to count distinct fruits (total number of fruits) .

SELECT COUNT(DISTINCT LEFT(`name`, LOCATE("Color:", `name`)-1)) as total_fruits
FROM exampe_table;

to be honest you want to normalize your database.. you can add another table to match colors or even redo this table and add another column. this would be a simplistic way of doing that

CREATE TABLE fruits_n_colors 
(`id` int, `name` varchar(255), `color` varchar(255))
    SELECT id, 
        SUBSTRING_INDEX(Name, ' Color: ', 1) as name, 
        SUBSTRING_INDEX(Name, ' Color: ', -1) as color
    FROM exampe_table
    ORDER BY id;

DEMO

on a side note if you would like to keep your current table then you can alter it and then update like so

ALTER TABLE exampe_table 
    ADD COLUMN `fruit` VARCHAR(55), 
    ADD COLUMN `color` VARCHAR(55);

UPDATE exampe_table et,
(
    SELECT 
        id,
        SUBSTRING_INDEX(Name, ' Color: ', 1) AS fruit, 
        SUBSTRING_INDEX(Name, ' Color: ', -1) AS color
    FROM exampe_table
)t
SET et.fruit = t.fruit, et.color = t.color WHERE et.id = t.id;

ALTER TABLE exampe_table 
    DROP COLUMN `Name`;

ANOTHER DEMO

Is it possible to sort a mysql table with regexp replace?

3 Answers3