CODEWITHSUNDEEP
mysqlsql
Chehmer
Chehmer

Reputation: 443

how to sort a word within a sting sorted alphabetic order using sql

i have an SQL table that have these kind of strings "Sapa to Hanoi".

i want to change it to "Hanoi to Sapa" because "Hanoi" should come first when sorting alphabet order.

how can i do it in SQL?

Upvotes: 1

Views: 726

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

It's another way to produce such a query :

select group_concat(distinct str_ separator ' ') as str
  from
(  
 select str_ from ( 
   select 
          q.n, 
          substring_index(substring_index(str, ' ', n), ' ', -1 ) as str_
     from tab
    inner join ( select ordinal_position as n
                   from INFORMATION_SCHEMA.COLUMNS t
                  where table_name='COLUMNS' ) q
       on char_length(str) >= n - 1
  ) q1
  order by str_  
) q2;

str
-------------
Hanoi Sapa to

for a string composed of three words.

Upvotes: 0

LukStorms
LukStorms

Reputation: 29647

For 3 words? Compare the first word with the last and stitch them differently if the order is wrong.

Example:

SELECT col, 
CASE 
WHEN (LENGTH(TRIM(col)) - LENGTH(REPLACE(TRIM(col), ' ', ''))+1) = 3 AND SUBSTRING_INDEX(TRIM(col),' ',1) > SUBSTRING_INDEX(TRIM(col),' ',-1)
THEN CONCAT(
  SUBSTRING_INDEX(TRIM(col),' ',-1), ' ', 
  SUBSTRING_INDEX(
   SUBSTRING_INDEX(TRIM(col),' ',2),' ',-1), 
   ' ',SUBSTRING_INDEX(TRIM(col),' ',1)) 
ELSE col 
END as sorted
FROM
(
    select 'Foo to Bar' as col 
    union all select 'Bar to Foo' 
    union all select 'Foo'
) q

Test here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

We can use SUBSTRING_INDEX to lexicographically compare the two city names, and then swap positions should the second name belong the first one.

SELECT
    field,
    CASE WHEN SUBSTRING_INDEX(field, ' to ', 1) < SUBSTRING_INDEX(field, ' to ', -1)
         THEN FIELD
         ELSE CONCAT(SUBSTRING_INDEX(field, ' to ', -1), ' to ', SUBSTRING_INDEX(field, ' to ', 1)) END AS new_field
FROM yourTable;

Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Well, if you just have two parts, you can reconstruct the string:

select concat(least( substring_index(col, ' to ', 1), substring_index(col, ' to ', -1),
              ' to ',
              greatest( substring_index(col, ' to ', 1), substring_index(col, ' to ', -1)
             )

However, I would advocate that you put the two city names in separate columns. Then construct the string when you retrieve the data.

Upvotes: 0

Related Questions