MySQL按字母顺序排序但忽略“the”

Question

我有一个MySQL数据库，其中有一个包含书籍数据的表。 表中的一个列名为“title”。 一些标题以单词“the”开头，而有些则不是。

例如：

- “The Book Title One” - “Book Title Two” - “Book Title Three”

我需要按字母顺序从数据库中提取这些书籍标题，但我需要忽略以“the”开头的标题中的“the”。

SQL（特别是MySQL）是否提供了在查询中执行此操作的方法？

- Joe Hoskinson

---

请注意，对标题进行排序比忽略“the”（以及“a”和“an”）要复杂得多。 “Les Misérables”应该排在哪里？（“les”是法语中几种表示“the”的方式之一）。《芝加哥手册》有一份长长的规则清单，介绍如何按字母顺序排列。 - derobert

Answer 1

使用CASE WHEN来检查列值是否以"The"开头，如果是，则返回标题但去掉"the"。这将成为您稍后用于排序的新列。

select title, case when title like 'The %' then trim(substr(title from 4)) else title end as title2 from tablename order by title2;

- mdprotacio

---

那么您的建议是我在数据库中创建一个完整的第二个标题列，从中提取排序顺序？基本上，我将保留当前的一列，并使用去掉开头的“the”的第二个标题列？ - Joe Hoskinson

另一列将是虚拟的，这意味着它不存在于表结构中，只会出现在查询结果中。 - mdprotacio

@JoeHoskinson并不是在建议你在查询时使用case语句创建列，然后按照该列排序。 - Ben English

错误的 like 参数：如果标题是These and Those会发生什么？ - pilcrow

然后在 The 后面加上空格 :D 我会纠正它的。:D - mdprotacio

Answer 2

您可以在ORDER BY 中使用 CASE 语句，同时使用REGEXP或LIKE来匹配以想要删除的单词开头的字符串。

在下面的例子中，我查找以a、an或the开头的所有单词，然后删除到空格为止的所有内容，并修剪掉额外的空格（可能会有两个或多个空格跟在the后面的情况）。

SELECT *
FROM books
ORDER BY 
  CASE 
    WHEN title REGEXP '^(A|An|The)[[:space:]]' = 1 THEN 
      TRIM(SUBSTR(title , INSTR(title ,' '))) 
    ELSE title
  END ;

- Daniel Gimenez

Answer 3

如果您确定永远不会犯错（并使用小写而不是大写）

select *
from books b 
order by UPPER(LTRIM(Replace(b.Title, 'The', '')))

否则，您的排序将会先排所有大写字母，再排所有小写字母。

例如，以下为升序排列：

Have a Great Day
Wild west
Zorro
aZtec fries are hotter
alfred goes shopping
bart is small
will i am not

改编自AJP的答案

- tony gil

Answer 4

我看到了一些复杂的答案，尝试了一下但是都不正确（没有起作用）或者不安全（替换了所有出现的“the”）。我相信解决方案很简单，或者可能是我理解有误或者没有考虑到边缘情况（真诚地，没有任何讽刺意味）。

... ORDER BY SUBSTRING(UPPER(fieldname), IF(fieldname LIKE 'The %', 5, 1))

如前所述，UPPER仅防止ASCII排序，该排序将B排在a之前（请注意大小写差异）。

当只有一个条件时，不需要使用switch-case语句，IF()就可以完成。

我正在使用MySQL 5.6，似乎字符串函数是从1开始计数的，与PHP中从0开始计数的字符串不同（这使我感到困惑）。我已在我的数据集上测试了以上内容，并且它有效。

- Luke Madhanga

Answer 5

select *
from books b 
order by LTRIM(Replace(b.Title, 'The', ''))

请注意，这将替换标题中的“the”...无论在标题中的位置如何。因此，请使用子字符串获取前3个字符。

- AJP

---

将以下与编程有关的内容从英语翻译成中文。仅返回翻译的文本：将单词“the”替换为“”，因为它所说的是。我应该先说前4个字符而不是3个，并使用“the ”而不是“the”。感谢您指出。 - AJP

Answer 6

简单：

SELECT Title
FROM book
ORDER BY IF(Title LIKE "The %", substr(Title, 5), Title);

解释：

我们使用IF函数来从字符串开头删除“ The”（如果存在），然后将字符串返回给ORDER BY子句。对于更复杂的字母顺序规则，我们可以创建一个用户定义的函数，并将其放置在ORDER BY子句中。然后您将拥有...ORDER BY MyFunction(Title)。

- Andrew