仅按字母数字字符排序SQLite中的ORDER BY

Question

我正在对SQLite中的歌曲进行排序（在Android上）。我想按以下顺序排列：

1. 不区分大小写
2. 以整数值为依据，在末尾带有前导数字。
3. 没有标点符号（例如括号，句号，连字符，撇号）

我已经完成了1和2 （见下文）。然而，我无法弥补每个字符（除了字母，数字和空格之外）的位置，除非调用replace()来代替每个字符。

是否有其他方法可以做到这一点，而不是调用replace()约32次？
（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

- Phrogz

---

@Phrogz，为什么不尝试获取原始字符串长度与将标点符号替换为“''”（空字符串）的字符串长度之差，并根据该差异对问题3进行排序呢？ - Vamsi Prabhala

我觉得你没有理解我的意思。我想表达的是，对于一个字符串 'ab,c'，它的长度为 4，而将标点符号替换为 '' 后的原始字符串长度为 3。这样，您就可以得到差异为 1。对于没有标点符号的字符串，这个差异将为 0。因此，您可以在 order by 子句中使用这些差异。希望你能明白我的意思。 - Vamsi Prabhala

@vkp，我理解你的意思，但我不明白这会如何有所帮助。也许我写的第三个要求有问题，你可能误解了我的意图。正如上面所示，我希望“L'accord”在“Labradors”和“Lagan”之间排序。那么，第一个的差异为diff:1，其他的为diff:0，这怎么能满足我的需求呢？ - Phrogz

当您存储数据时，请创建一个经过清理的排序键。在提取数据时按该键进行排序。这将使您的生活变得更加简单。 - Allan S. Hansen

@AllanS.Hansen 这是一个对从 MP3 ID3 标签中实时提取的数据执行的 SQLite 查询。该数据库无法被修改。 - Phrogz

Answer 1

我建议在表中添加一个名为“SortingName”的附加列。在插入时计算此值，最好不要使用SQL，而是在更高级别的语言中使用所有这些漂亮的字符串操作。

我并没有完全理解这个数字问题。我猜你可以在插入之前提取数字并将其放入另一列，如“SortingNumber”。

然后只需像这样排序：

Order By
  SortingName,
  SortingNumber

(或者反过来。)

另一个优点是性能。通常，您读取数据的频率比写入数据的频率更高。您甚至可以在这两个排序列上创建索引，如果在查询中计算它，则通常不可能实现此操作。

- Stefan Steinegger

---

抱歉，从我的角度来看，数据库是只读的。该表是根据找到的歌曲元数据动态生成的。我可能能够添加第二个包含此信息的表，但随着新歌曲的出现或消失，我需要担心表格不同步的问题。 - Phrogz

Answer 2

第一种解决方案（当数据库和应用程序可以修改时）：

在表格中添加一个单列，例如solumntForSorting。然后在插入之前，在您的应用程序中将第二个条件（“以整数值结尾的前导数字”）与从不需要的符号中首次“清除”的歌曲名称连接为0或1。因此，solumntForSorting将会得到以下内容：0Im a Rainbow Too 和 1911 is a Joke。

第二种解决方案（只有应用程序可以修改时）：

如果您必须按照某些符号对数据进行排序，并且不允许更改数据库，则由于过滤不需要的值而导致选择速度较慢。大部分开销都会在CPU时间和内存上。

我认为使用replace函数很繁琐，这就是为什么我建议使用CTE与要删除的值列表，例如（‘。’，‘。’，‘；’，‘（’，‘）’，‘’’，‘-’）。 CTE将像多个替换一样笨重，但更易于修改和维护。

尝试这个解决方案：

 WITH RECURSIVE 
 ordering_name_substr(len, name, subsstr, hex_subsstr, number) 
 AS (SELECT  length(name), name, substr(name, 1, 1), hex(substr(name, 1, 1)), 1  
       FROM songs
      UNION ALL 
     SELECT len, name, substr(name, number + 1, 1),
            hex(substr(name, number + 1, 1)), number + 1
       FROM ordering_name_substr WHERE number < len),
 last_order_cretaria(value, old_name)
  AS (select GROUP_CONCAT(subsstr, ''), name 
           from ordering_name_substr 
        where hex_subsstr not in
       ('28', '29', '2C', '2E', '27') group by name )

SELECT S.n, S.name
FROM songs AS S LEFT JOIN last_order_cretaria AS OC
ON S.name = OC.old_name
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
         OC.value
  END
COLLATE NOCASE

我已在 sqlfiddle 上进行了测试。

在列表 ('28', '29', '2C', '2E', '27') 中，这些是 ASCII 码值（十六进制表示），你需要转义它们以避免影响排序。

你也可以尝试使用这些字符本身：('.', '.', ';', '(', ')', '''', '-')。

WITH RECURSIVE 
 ordering_name_substr(len, name, subsstr, number) 
 AS (SELECT length(name), name, substr(name, 1, 1), 1  
       FROM songs
      UNION ALL 
     SELECT len, name, substr(name, number + 1, 1),
            number + 1
       FROM ordering_name_substr WHERE number < len),
 last_order_cretaria(value, old_name)
  AS (select GROUP_CONCAT(subsstr, ''), name 
           from ordering_name_substr 
        where subsstr not in
       ('.', '.', ';', '(', ')', '''', '-') group by name )

SELECT S.n, S.name
FROM songs AS S LEFT JOIN last_order_cretaria AS OC
ON S.name = OC.old_name
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
         OC.value
  END
COLLATE NOCASE

为了使排序快速简单，您需要能够更改您的数据库和应用程序。

- Mikhailov Valentin

Answer 3

在我看来，最高效的方法是创建一个触发器来填充一个名为sort_key的新字段。您需要一个主键。

CREATE TABLE songs (n INTEGER, name TEXT, 
                    sort_key TEXT, 
                    ID INTEGER PRIMARY KEY AUTOINCREMENT);

CREATE TRIGGER songs_key_trigger
    AFTER INSERT ON songs FOR EACH ROW
    BEGIN n
        Declare @sort_key as varchar(255)
        -- calculate and call here your slugify function
        -- to fill sort_key from 'new.n' and 'new.name'
        UPDATE songs 
          SET sort_key = @sort_key
          WHERE ID = new.ID;
    END

请注意，这种方法对索引友好，您可以在新列上创建索引以避免表的全表扫描操作。

- dani herrera

Answer 4

您可以使用sqlite3 Android NDK Bindings通过JNI调用来访问完整的sqlite3 c API。

然后，您可以使用sqlite3_create_collation_v2()和相关函数定义新排序序列。

这种方法不会改变数据库，因为排序仅在当前数据库连接上被覆盖。因此，它满足了只读数据库的要求。

请注意，我说您可以这样做，但并不是说您应该这样做！权衡利弊，大多数情况下，这样做可能不值得额外的努力。

- user700390

Answer 5

如果您被允许创建函数，这是我要创建的函数（取自如何从SQL Server中的字符串中删除所有非字母字符？并进行了一些修改）：

Create Function [dbo].[RemoveNonAlphaNumericCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-zA-Z0-9\s]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

这将满足您的第三个要求并清除字符串中所有无用的内容，然后您的查询将如下所示：

SELECT n
FROM songs
ORDER BY
  CASE WHEN [dbo].[RemoveNonAlphaNumericCharacters](name) GLOB '[0-9]*' THEN 1
       ELSE 0
  END,
  CASE WHEN [dbo].[RemoveNonAlphaNumericCharacters](name) GLOB '[0-9]*' THEN CAST(name AS INT)
       ELSE [dbo].[RemoveNonAlphaNumericCharacters](name)
  END
COLLATE NOCASE

看起来不美观，可能性能也不是最好的。我会采取Stefan建议的方法。解析您的歌曲名称并将修剪后的名称插入到一个单独的列中进行排序（当然，在该列上有索引）。这应该是最佳解决方案。

- Evaldas Buinauskas

---

谢谢您的想法。我认为这不可能，因为这不是我的数据库，而是由媒体管理器提供的。https://dev59.com/BWsz5IYBdhLWcg3wfn0x#8283265 - Phrogz

@Phrogz 当我查看标记的产品时，我应该更加小心。我想除了手动用30多个替换去除所有垃圾外，没有其他真正的方法了。 - Evaldas Buinauskas