CODEWITHSUNDEEP
mysql
ameenulla0007
ameenulla0007

Reputation: 2683

Is Value sorting possible in MySQL?

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?

Upvotes: 1

Views: 63

Answers (1)

Iłya Bursov
Iłya Bursov

Reputation: 24156

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

Upvotes: 1

Related Questions