Is Value sorting possible in MySQL?

Question

Example:

If I have a database table named products and a column within named product_title

contents under product_title

zyx
cba
defabc

What I require?

`zyx` should be changed `xyz`
`cba` should be changed `abc`
`defabc` should be changed `abcdef`

All values in alphabetical order.

What I tried?

I tried searching for the requirement, but I couldn't find any solution for it. I just want to know "is this possible"?

if this is not possible, how can i sort my records with most matched substring?

Not sure I'm understanding. Are you wanting to update the data -- update table set field = 'xyz' where field = 'zyx'? — sgeddes
– sgeddes, Commented Mar 4, 2016 at 19:38
Try this function - Reverse() dev.mysql.com/doc/refman/5.7/en/string-functions.html — xardael
– xardael, Commented Mar 4, 2016 at 19:40
not exactly @sgeddes, can't we cast the field temporarily to sort in alphabetical order? — ameenulla0007
– ameenulla0007, Commented Mar 4, 2016 at 19:40
I believe the OP is asking if MySQL is able to sort the values string literal alphabetically after selection. @ameenulla0007 please confirm that is what you want. — David J Eddy
– David J Eddy, Commented Mar 4, 2016 at 19:40
Why do you need to sort the contents of the columns alphabetically? You wouldn't be storing more than one piece of data in a column, would you? — Tom H
– Tom H, Commented Mar 4, 2016 at 19:42

Iłya Bursov · Accepted Answer · 2016-03-04 20:29:10Z

1

there is no build-in function to sort symbols in string in mysql.

anyway you can create your own stored procedure:

CREATE FUNCTION sv(
    x VARCHAR(255)
)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    declare r varchar(255);
    declare maxlen int;
    declare pos int;
    declare sym char(1);
    declare npos int;

    set r = "";

    set maxlen = length(x);
    set pos = 1;
    while (pos <= maxlen) do
        set sym = substr(x, pos, 1);

        set npos = 1;
        while ((npos <= length(r)) and (substr(r, npos, 1) < sym)) do
            set npos = npos + 1;
        end while;
        set r = concat(substr(r, 1, npos-1), sym, substr(r, npos));

        set pos = pos + 1;
    END while;

    return r;
END

fiddle

it works quite slow, to speed up process I suggest you to create new column product_title_sorted, run update products set product_title_sorted=sv(product_title) where product_title_sorted is null

and then use product_title_sorted in your queries

answered Mar 4, 2016 at 20:29

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

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

3 Comments

ameenulla0007 Over a year ago

This is great, but I would I like to know, is this performance oriented?

Iłya Bursov Over a year ago

No, it is slow approach

ameenulla0007 Over a year ago

Oh! But thanks for the response and help @lashane. Thank you.. :)

Collectives™ on Stack Overflow

Is Value sorting possible in MySQL?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related