How can I sort characters in a string field alphabetically in SQLite? ("sokoban"--->"abknoos" )

Question

How can I sort the characters in a string value in SQLite Database? For example:

"macedonia" --sorted--> "aacdeimno"?

I know one method: if I use another table: integers(i int) (1,2,3,4,,,,,100) and make this query:

SELECT group_concat(l ,''  )
FROM 
  ( SELECT substr('macedonia',i,1) as l 
    from  integers 
    where i BETWEEN 1 and length('macedonia') 
    ORDER BY l 
  )

-- SORT WORKS ! --> 'macedonia'---> 'aacdeimno'

But this method is too complex if I use it inside other complex queries ! Howto pass string 'macedonia' as parameter of a query ? In MS Access there are so called PARAMETER QUERIES ...

Do you know something more simple ? Maybe using CTE, or REGEXP ?? For PostgreSQL database I wrote my own function psrt() to sort strings in PL/pgSQL Language. Is there such SORTING function available for SQLite ?

Answer 1

Perhaps consider the following recursive CTE based example:-

DROP TABLE IF EXISTS demo;
CREATE TABLE IF NOT EXISTS demo (s TEXT);
/* Load some test data */
INSERT INTO demo VALUES
    (''),('macedonia'),('blah'),('rumplestiltskin'),('something'),(100),(null);
/* Show loaded data */
SELECT rowid,* FROM demo;

/* The reordering via CTEs */
WITH cte1(r,current,rest) AS (
    SELECT rowid,substr(s,1,1), substr(s,2) FROM demo
    UNION ALL 
        SELECT r,substr(rest,1,1),substr(rest,2) 
        FROM cte1 
        WHERE length(rest) > 0
        LIMIT 50
),
cte2 AS (SELECT * FROM cte1 ORDER BY r,current)
SELECT r,group_concat(current,'') FROM cte2 GROUP BY r
;
/* Cleanup demo environment */
DROP TABLE IF EXISTS demo;

This outputs:-

1. The original data as
   1. , and
2. The reordered as
   1. 

• note rowid is used in the absence of a primary key to tie each individual letter/character to it's row. Obviously will not work with WITHOUT ROWID tables but could be adapted to then utilise the primary key.

• note LIMIT 50 was used to ensure that the loop ends (just in case), it will limit the above and should either be removed or increased to allow all data to be processed.

• note that the above is case dependant so ZzXxYyWwUuAaBbCc will be re-ordered to be ABCUWXYZabcuwxyz, so adjustments to the ORDER may be required.