1

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?

ameenulla0007
  • 2,663
  • 1
  • 12
  • 15
  • Not sure I'm understanding. Are you wanting to `update` the data -- `update table set field = 'xyz' where field = 'zyx'`? – sgeddes Mar 04 '16 at 19:38
  • Try this function - Reverse() http://dev.mysql.com/doc/refman/5.7/en/string-functions.html – xardael Mar 04 '16 at 19:40
  • not exactly @sgeddes, can't we cast the field temporarily to sort in alphabetical order? – ameenulla0007 Mar 04 '16 at 19:40
  • 1
    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 Mar 04 '16 at 19:40
  • yup exactly, @Pheagey. i just wanna have temporary sorting of field values for comparison under condition.. – ameenulla0007 Mar 04 '16 at 19:42
  • 1
    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 Mar 04 '16 at 19:42
  • YEa.. `Reverse()` isn't going to work for that one. There is interesting answer (the second one) [at this question](http://stackoverflow.com/questions/21232979/mysql-alphabetical-order-applied-to-a-string), with a "Numbers" table and some `substring()` sneakery that looks promising (haven't tried it myself). – JNevill Mar 04 '16 at 19:45
  • the idea is to get exact INDEX of the substring, through which i can utilize `LOCATE` function in order to list out most matching records. – ameenulla0007 Mar 04 '16 at 19:45
  • Because there is no split functionality in MYSQL it will be complicated. – maraca Mar 04 '16 at 19:49
  • then how to get most matched substring, find the finest index and sort it accordingly? @maraca – ameenulla0007 Mar 04 '16 at 19:52
  • If you want to use stored procedures it could be done. E.g. if you don't care about performance you can implement a bubble sort with substring and reverse ;-) – maraca Mar 04 '16 at 19:54
  • :D cool, but most importantly i don't wanna lose performance here. but i can have stored procedures, there is no issue with it.. – ameenulla0007 Mar 04 '16 at 19:56
  • is the number of choices limited to a managable amount? Like a-f and x-z? – maraca Mar 04 '16 at 20:02
  • No there is no limitation here, as the table contains product info, product title would be anything. – ameenulla0007 Mar 04 '16 at 20:05
  • I'm asking because you could count how many times one specific character appears in a string by doing the following (for example 'A' in String s): length(s) - length(replace('A', '', s)) – maraca Mar 04 '16 at 20:10
  • But, doesn't this lower down the performance? Because it can also be lengthier string to check. – ameenulla0007 Mar 04 '16 at 20:13
  • It might be best if you posed your actual question/requirement. If you're dealing with product names then this doesn't make any sense. If I search for "desserts" I don't want to find "stressed". – Tom H Mar 04 '16 at 20:14
  • @ameenulla0007 I think the best best is to return the select results, then use a logic language to sort the array/object/collection of results by the alphabetical values. Something like this http://stackoverflow.com/questions/9912469/php-how-to-sort-the-characters-in-a-string – David J Eddy Mar 04 '16 at 20:14
  • @pheagey if I limit down SQL records to 3, then the most matched string record would not be available in the result. – ameenulla0007 Mar 04 '16 at 20:17

1 Answers1

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

Iłya Bursov
  • 23,342
  • 4
  • 33
  • 57