It is possible to sort particular cell value in mySql?

Question

I need to sort below cell values using mysql

Example:

cell contain red,blue,green
But I want that in alphabetic order.

This would be a difficult thing to do (I think), and would likely involve spreading those colors across rows only to aggregate them again in some order. For best results, just store each color in a separate row. So what I'm saying is that you should improve your database design. — Tim Biegeleisen, Jan 03 '18 at 11:08
The problem is that its not a list of value**s** at all, is just a single value from the perspective of the database. You should modify the design so each value is in a column on its own row. — Alex K., Jan 03 '18 at 11:09

Sanjay Kumar Singh · Answer 1 · 2018-01-03T12:38:13.880

Steps to do this,

1.First you need to make a procedure call for sorting values

2.Call your procedure then

Here is the code to create mysql procedure

-- sort comma separated substrings with unoptimized bubble sort
DROP FUNCTION IF EXISTS sortString;
DELIMITER |
CREATE FUNCTION sortString(inString TEXT) RETURNS TEXT
BEGIN
  DECLARE delim CHAR(1) DEFAULT ','; -- delimiter 
  DECLARE strings INT DEFAULT 0;     -- number of substrings
  DECLARE forward INT DEFAULT 1;     -- index for traverse forward thru substrings
  DECLARE backward INT;   -- index for traverse backward thru substrings, position in calc. substrings
  DECLARE remain TEXT;               -- work area for calc. no of substrings
-- swap areas TEXT for string compare, INT for numeric compare
  DECLARE swap1 TEXT;                 -- left substring to swap
  DECLARE swap2 TEXT;                 -- right substring to swap
  SET remain = inString;
  SET backward = LOCATE(delim, remain);
  WHILE backward != 0 DO
    SET strings = strings + 1;
    SET backward = LOCATE(delim, remain);
    SET remain = SUBSTRING(remain, backward+1);
  END WHILE;
  IF strings < 2 THEN RETURN inString; END IF;
  REPEAT
    SET backward = strings;
    REPEAT
      SET swap1 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward-1),delim,-1);
      SET swap2 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward),delim,-1);
      IF  swap1 > swap2 THEN
        SET inString = TRIM(BOTH delim FROM CONCAT_WS(delim
        ,SUBSTRING_INDEX(inString,delim,backward-2)
        ,swap2,swap1
        ,SUBSTRING_INDEX(inString,delim,(backward-strings))));
      END IF;
      SET backward = backward - 1;
    UNTIL backward < 2 END REPEAT;
    SET forward = forward +1;
  UNTIL forward + 1 > strings
  END REPEAT;
RETURN inString;
END |
DELIMITER ;

To make procedure call just you have to use,

-- example call:
SET @Xstr  = "red,blue,green"; // for query purpose only you need to write within (SQL Query here for that row)

SELECT sortString(@Xstr) AS s1

Please see the documentation guide map Click here to read

Also there is an alternative way to do if you are interested to study is that about FIND_IN_SET, please you can find some idea from one of the question from stackoverflow. Click here to read

score 1 · Accepted Answer · answered Jan 03 '18 at 12:24

You can create a function which sorts the items in the column:

create function f_comma_list_order ( t text )
returns text
begin

declare v_c int;

drop temporary table if exists tmp;
create temporary table tmp ( v text );


set v_c = 1;

while( v_c > 0 ) do
  select locate(',', t) into v_c;

  if (v_c>0) then
    insert into tmp select substr(t, 1, v_c-1);
    set t = substr(t, v_c+1);
  else 
    insert into tmp values (t);
  end if;
end while;

select group_concat(v order by v) into t
from tmp;

return t;

end

and then call the function:

select f_comma_list_order('red,green,blue')

It is possible to sort particular cell value in mySql?

2 Answers2