ORDER BY字母数字字符仅在SQLite中

概述我在SQLite(在 Android上)排序歌曲.我想订购他们： >不区分大小写 >以最后的前导数字,以整数值表示. >没有标点符号(例如括号,句点,连字号,撇号) 我有1& 2工作(见下文).但是,除了为每个字符调用replace()之外,我不知道如何替换每个字符(字母,数字和空格除外). 有没有办法这样做,而不是〜32个call来替换()？ (ASCII值33-47,58-64,91-96,1 我在sqlite(在 Android上)排序歌曲.我想订购他们：

>不区分大小写
>以最后的前导数字,以整数值表示.
>没有标点符号(例如括号,句点,连字号,撇号)

我有1& 2工作(见下文).但是,除了为每个字符调用replace()之外,我不知道如何替换每个字符(字母,数字和空格除外).

有没有办法这样做,而不是〜32个call来替换()？
(ASCII值33-47,58-64,91-96,123-126)

这是一个测试表.理想情况下,值’n’按顺序排列. (不,你不能按n订购)

create table songs (n integer,name text);insert into songs (n,name) values (6,'I''ll Be That Girl');insert into songs (n,name) values (24,'1969');insert into songs (n,name) values (9,'La Moldau');insert into songs (n,name) values (20,'Pule');insert into songs (n,name) values (7,'I''m a Rainbow Too');insert into songs (n,name) values (21,'5 Years');insert into songs (n,name) values (18,'Pressure');insert into songs (n,name) values (13,'Lagan');insert into songs (n,name) values (1,'any old wind that blows');insert into songs (n,name) values (17,'Poles Apart');insert into songs (n,name) values (8,'Imagine');insert into songs (n,name) values (14,'Last Stop before Heaven');insert into songs (n,name) values (3,'I Before E Except After C');insert into songs (n,name) values (4,'i do,i do,i do');insert into songs (n,name) values (22,'99 Luftballons');insert into songs (n,name) values (12,'L''accord parfait');insert into songs (n,name) values (15,'Pluto');insert into songs (n,name) values (19,'The Promise');insert into songs (n,name) values (2,'(Don''t Fear) The Reaper');insert into songs (n,name) values (10,'L.A. Nights');insert into songs (n,name) values (23,'911 is a Joke');insert into songs (n,name) values (5,'Ichthyosaurs Are Awesome');insert into songs (n,name) values (11,'Labradors are lovely');insert into songs (n,name) values (16,'P.O.D.-Boom');

这是解决方案只有1& 2上图：

SELECT nFROM songsORDER BY  CASE WHEN name GLOB '[0-9]*' THEN 1       ELSE 0  END,CASE WHEN name GLOB '[0-9]*' THEN CAST(name AS INT)       ELSE name  ENDColLATE NOCASE

对于该测试集,它按照以下顺序产生结果：2,1,3,4,6,7,5,8,12,10,9,11,13,14,16,15,17,18,20,19,21,22,23,24

我可以修复这个特定的测试集,手动替换每个不需要的字符：

SELECT nFROM songsORDER BY  CASE WHEN name GLOB '[0-9]*' THEN 1       ELSE 0  END,CASE WHEN name GLOB '[0-9]*' THEN CAST(name AS INT)       ELSE         replace(           replace(             replace(               replace(name,'.',''),'(',''             ),'''',''           ),'  ',' '         )  ENDColLATE NOCASE

我将在表中添加一个名为“Sortingname”或其他的列.在插入时计算此值,理想情况下不在sql中,而是在具有所有这些漂亮字符串 *** 作的较高级别语言中.

我没有真正了解这个数字.我猜你最简单的事情是在插入之前提取数字,并将其放入另一个列,如“SortingNumber”.

然后简单地排列如下：

Order By  Sortingname,SortingNumber

(或者相反的方法)

另一个优点是性能.你通常读取数据更多,然后你写.您甚至可以在这两个排序列上创建索引,如果在查询中计算索引列,通常是不可能的.

总结

以上是内存溢出为你收集整理的ORDER BY字母数字字符仅在SQLite中全部内容，希望文章能够帮你解决ORDER BY字母数字字符仅在SQLite中所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。