Sorting string characters in MySQL

Question

I have a column (varchar(255)) in a mysql table, lets call it "word". How to write a select query that returns me the values in this column sorted by characters in the string?

For example, if one of the records had the word "earth" it should return me "aehrt" and so on, for all the rows. Is there any way to do it in a single query?

Answer 1

Probably highly inefficient, but without any need for user-defined functions:

SELECT GROUP_CONCAT(LETTER SEPARATOR '') AS ReOrderedLetters
  FROM ( SELECT 'A' as LETTER FROM <table> WHERE UPPER(`name`) like '%A%'
         UNION ALL
         SELECT 'B' as LETTER FROM <table> WHERE UPPER(`name`) like '%B%'
         UNION ALL
         SELECT 'C' as LETTER FROM <table> WHERE UPPER(`name`) like '%C%'
         UNION ALL
         SELECT 'D' as LETTER FROM <table> WHERE UPPER(`name`) like '%D%'
         ...
         UNION ALL
         SELECT 'Y' as LETTER FROM <table> WHERE UPPER(`name`) like '%Y%'
         UNION ALL
         SELECT 'Z' as LETTER FROM <table> WHERE UPPER(`name`) like '%Z%'
       ) alpha

EDIT

I had to come up with a better alternative before going to bed, otherwise I'd never have got to sleep; so here's a much cleaner and more efficient alternative.

I created a table called letters with a single column of VARCHAR(1) called letter; then populated that table with the letters A to Z

CREATE TABLE IF NOT EXISTS `letters` (
   `letter` varchar(1) NOT NULL,
   PRIMARY KEY (`letter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `letters` (`letter`) VALUES
('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),
('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z');

then:

select U.`name`,
       GROUP_CONCAT(L.`letter` 
           ORDER BY L.`letter` ASC 
          SEPARATOR '') AS ReOrderedLetters 
  FROM `users` U 
  LEFT JOIN `letters` L ON POSITION(L.`letter` IN UPPER(U.`name`)) > 0
 GROUP BY U.`name`

Answer 2

Alright kids. Here is a bubblesort function for t-sql. You can convert to mysql yourself. It turns out that SQL Server really doesn't like recursive functions, but this actually ran pretty quickly.

Create Function bubblesort(@In varchar(255))
 Returns varchar(255)
AS
BEGIN
  Declare @Answer varchar(255)
  Declare @swapped bit
  Declare @Counter int

  Set @Answer = @In;
    --only need to sort strings longer than 1
  if len(@Answer) > 1
    BEGIN
      While 1=1
        BEGIN
          Set @Counter = 1;
          Set @swapped = 0;
          While @Counter <= len(@Answer) - 1
            BEGIN
                If substring(@Answer, @Counter, 1) > substring(@Answer, @Counter + 1, 1)
                  BEGIN
                    --swap
                    Set @swapped = 1;
                    Set @Answer = Stuff(@Answer, @Counter, 2, reverse(substring(@Answer, @Counter, 2)));
                  END;
                Set @Counter = @Counter + 1;
            END;
          if @swapped = 0
            BREAK;
        END;
    END;
  Return @Answer; 
END;
go

Don't forget to trim the results

Select ltrim(myfield) from mytable;

Answer 3

It inserts each letter of each word into a temporary table and then group_concat()s them sorted. This probably isn't a very efficient solution due to the use of a cursor and the insertion of all the letters into a temp table. I'm betting a sort routine will be faster, but you need some kind of array structure to hold the letters if you won't use a temp table. I'm not sure if MySQL procedures have arrays.

delimiter $$
drop procedure if exists sort_letters$$
create procedure sort_letters()
begin
        declare done int default 0;
        declare word varchar(256);
        declare cur1 cursor for select i from a;
        declare continue handler for not found set done = 1;
        drop temporary table if exists temp;
        create temporary table temp (id int, letter char);
        set @wordcount = 0;
        open cur1;
        repeat
                fetch cur1 into word;
                if not done then
                        set @counter = 0;
                        set @len = length(word);
                        while (@counter <= @len) do
                                insert into temp values
                                (@wordcount, substring(word,@counter,1));
                                set @counter = @counter + 1;
                        end while;
                        set @wordcount = @wordcount + 1;
                end if;
        until done end repeat;
        close cur1;
        select group_concat(letter order by letter separator '') 
        from temp group by id;
end$$


mysql> desc a;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| i     | varchar(256) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.04 sec)

mysql> select * from a;
+-------+
| i     |
+-------+
| earth |
| sand  |
| fire  |
+-------+
3 rows in set (0.00 sec)

mysql> call sort_letters();
+---------------------------------------------------+
| group_concat(letter order by letter separator '') |
+---------------------------------------------------+
| aehrt                                             |
| adns                                              |
| efir                                              |
+---------------------------------------------------+
3 rows in set (0.00 sec)

Answer 4

Here's an interesting alternative that would represent each character once. Not exactly sure if representing all the letters triggers some sort of overflow error, but the logic is food for thought:

SELECT
CONCAT(
REPEAT('a',SIGN(POSITION('a' IN word_col))),
REPEAT('b',SIGN(POSITION('b' IN word_col))),
REPEAT('c',SIGN(POSITION('c' IN word_col))),
REPEAT('d',SIGN(POSITION('d' IN word_col))),
REPEAT('e',SIGN(POSITION('e' IN word_col))),
REPEAT('f',SIGN(POSITION('f' IN word_col))),
...
REPEAT('Z',SIGN(POSITION('Z' IN word_col)))
)
FROM
word

Obviously, the "sort" is based on how the CONCAT is structured.