Text with numbers: how to get natural sort order

(1) By GGK (GGK_01) on 2022-03-31 08:27:03 [link] [source]

Hi,

creating a table like: CREATE TABLE "t" ("id" TEXT);

insert into t values('Title 1'),('Title 10'),('Title 2'),('Title 11'),('Title 21'),('Title 3'),('Title 4'), ('Title 5'),('Title 6'),('Title 7'),('Title 8');

and later sorting the id column, I get Title 1 Title 10 Title 11 Title 2 Title 21 Title 3 Title 4 Title 5 Title 6 Title 7 Title 8

But I need to get the results in natural sord order. How can I achieve this?

Thanks GGK

(2) By Adrian Ho (lexfiend) on 2022-03-31 08:58:13 in reply to 1 [link] [source]

See if this extension by Dr. Hipp is useful to you.

(3) By GGK (GGK_01) on 2022-03-31 09:12:19 in reply to 2 [link] [source]

thanks. No it isn't cause I use SQLite on Android with Java and I'm not familiar with C.

GGK

(4.1) By curmudgeon on 2022-03-31 09:35:38 edited from 4.0 in reply to 1 [link] [source]

would order by substr(id,7)+0 work?

(5) By GGK (GGK_01) on 2022-03-31 10:38:01 in reply to 4.1 [link] [source]

Thank you. that would already work for the example I gave. However, if the entry is not "Title" but something else, it does not work with that either.

(8.1) By curmudgeon on 2022-03-31 12:15:44 edited from 8.0 in reply to 5 [link] [source]

Would you not be better with
create table tbl(str TEXT, id INTEGER);

and

select str || ' ' || id as TitledID from tbl order by str, id;

(6) By Gunter Hick (gunter_hick) on 2022-03-31 11:24:00 in reply to 1 [source]

There is no built in function for sorting "arbitrary text intermingled with stuff that looks like numbers", only for sorting numerical data in numerical order, and for textual data in lexical (and some other) order(s).

Since you do not present the real problem you are trying to solve instead of a severely incomplete straw man example, I can only suspect that your design process has gone wrong.

If you inserted the records in natural sort order, then a simple 
SELECT <fieldname> FROM t ORDER BY rowid
would suffice.

The general case can be solved by defining your own collation sequence that does exactly what you need WRT to your undisclosed real problem.

(9) By GGK (GGK_01) on 2022-03-31 12:39:01 in reply to 6 [link] [source]

Thanks.

So I will solve it in program code

GGK

(7) By anonymous on 2022-03-31 11:59:29 in reply to 1 [link] [source]

'01Title' or 'Title01'?

(10) By GGK (GGK_01) on 2022-03-31 19:13:18 in reply to 7 [link] [source]

Thanks, solved it in code.

GGK