仅在 SQLite 中按字母数字字符排序 [英] ORDER BY alphanumeric characters only in SQLite
问题描述
我正在 SQLite(在 Android 上)对歌曲进行排序.我想订购它们:
I am sorting songs in SQLite (on Android). I want to order them:
- 不区分大小写
- 以整数值结尾的前导数字.
- 没有标点符号(例如括号、句号、连字符、撇号)
我有 1 &2 工作 (见下文).但是，除了为每个字符调用 replace() 之外，我无法弄清楚如何替换每个字符(字母、数字和空格除外).
I have 1 & 2 working (see below). However, I can't figure out how to replace every character (other than letters, numbers, and spaces) other than to call replace() for each character.
除了 32 次调用 replace() 之外，还有其他方法可以做到这一点吗?
(ASCII 值 33-47,58-64,91-96,123-126)
Is there a way to do this other than ~32 calls to replace()?
(ASCII values 33-47,58-64,91-96,123-126)
这是一个测试表.理想情况下，值 'n' 应该按顺序出现.(不，您不能按 n 订购；)
Here is a test table. The value 'n' should ideally come out in order. (No, you cannot order by 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 以上:
Here's the solution to just 1 & 2 above:
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
For this test set it produces results in this order: 2,1,3,4,6,7,5,8,12,10,9,11,13,14,16,15,17,18,20,19,21,22,23,24
我可以通过手动替换每个不需要的字符来修复这个特定的测试集:
I can fix this particular test set with manual replaces for each undesired character:
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
推荐答案
我会在表中添加一个额外的列，称为SortingName"或其他名称.插入时计算这个值，最好不要用 SQL，而是用高级语言，在那里你有所有这些漂亮的字符串操作.
I would add an additional column in the table, called "SortingName" or something. Calculate this value when inserting, ideally not in SQL but in a higher level language where you have all these nice string operations.
我并没有真正理解这个数字.我想您能做的最简单的事情是在插入之前提取数字并将其放入另一列，例如SortingNumber".
I didn't really understand this thing with the number. I guess the simplest thing you can do is extract the number before insert and put it into another column, like "SortingNumber".
然后简单地这样排序:
Order By
SortingName,
SortingNumber
(或者反过来.)
另一个优势是性能.您通常会比写入数据更频繁地读取数据.您甚至可以在这两个排序列上创建索引，如果在查询中计算通常是不可能的.
Another advantage is performance. You usually read data much more often then you write it. You can even create indexes on these two sorting columns, which is usually not possible if you calculate it in the query.
这篇关于仅在 SQLite 中按字母数字字符排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
