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

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

我有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 n
FROM songs
ORDER 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
  END
COLLATE 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 n
FROM songs
ORDER 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,'.',''),'(',''
             ),'''',''
           ),'  ',' '
         )
  END
COLLATE NOCASE

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

然后简单地排列如下：

Order By
  SortingName,SortingNumber

(或者相反的方法)

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!