Как я могу отсортировать SQLite-запрос, игнорируя статьи ( "the", "a" и т.д.)?

Я использую С# для отображения списка названий фильмов, которые я вызываю из базы данных SQLite. В настоящее время я использую собственный класс ListBox, который имеет функцию для сортировки текста, разделяющего слово "The" с начала каждого элемента. Однако это не совсем простой способ сделать это, поскольку он вызывает базу данных SQLite и затем сортирует. Я бы предпочел сократить его до одного шага, надеюсь, что он будет сортироваться прямо из базы данных в моем запросе "SELECT".

Я сделал несколько поисков по этому вопросу и нашел некоторые предложения, в том числе создание дополнительного столбца сортировки в базе данных. Хотя это, безусловно, возможность, мне интересно, есть ли более простые варианты, которые не требуют вставки почти идентичной повторяющейся информации (особенно, если база данных становится больше). Я новичок в SQLite, но я кое-что прочитал о создании функции сортировки, которая может использоваться для создания пользовательского заказа. Тем не менее, я не уверен, подходит ли это для этого и не может найти никакой помощи в его реализации на С#.

Было надеяться, что кто-то сможет поделиться некоторыми рекомендациями. Если дополнительный столбец сортировки - лучший способ, то это то, что я буду делать.

Ответ 1

Чтобы не вставлять повторяющиеся данные, о наличии двух столбцов: TITLE_PREFIX (обычно пустой, но иногда содержит "The" или "A", индекс не указан в этом столбце) и TITLE (содержит название без "The" или "A", это столбец, в который вы создаете индекс). Чтобы отобразить данные, вам необходимо объединить TITLE_PREFIX и TITLE. Но вы просто ищете TITLE.

Ответ 2

Вот решение:

ORDER BY (CASE 
    WHEN sortTitle LIKE 'the %' THEN substr(sortTitle,5) 
    WHEN sortTitle LIKE 'a %' THEN substr(sortTitle,3) 
    WHEN sortTitle LIKE 'an %' THEN substr(sortTitle,4) 
    ELSE sortTitle END)

Ответ 3

Вы можете сохранить каждый заголовок в двух частях: title и prefix.

С SQLite вы можете объединить 2 строковых значения через || operator, также известный как concatenate operator.

Вот пример:

SELECT prefix || ' ' || title FROM movies ORDER BY title

Вы также можете использовать ltrim, если префикс пуст, поэтому у вас нет места спереди:

SELECT ltrim(prefix || ' ' || title) FROM movies ORDER BY title

Другой альтернативой является сохранение префикса в конце заголовка. Например, во многих магазинах фильмов вы увидите что-то вроде:

Три мушкетера, The

Ответ 4

Внутри кода С#

Если вы хотите сделать это в С#, используйте LINQ, чтобы сделать заказ для вас. Я опубликовал полный образец в PasteBin. Это позволит вам:

избегать дублирования данных в вашей базе данных
используйте индексы DB, как обычно, независимо от того, какие RDBMS
помещать шумовые слова в файл конфигурации, тем самым уменьшая время простоя/перестраивая/передислоцируя при изменении списка
убедитесь, что решение более читаемо в коде клиента

DropDownList1.DataSource = myBooks.OrderBy(n => ReplaceNoise(n.Title))

public string ReplaceNoise(string input)
{
     string[] noise = new string[] { "the", "an", "a" };

     //surely this could be LINQ'd 
     foreach (string n in noise)
     {
         if (input.ToLower().StartsWith(n))
         {
             return input.Substring(n.Length).Trim();
         }
     }
     return input;
}

В вашем заявлении SQLite

Как просто заменить шумовые слова пробелами в порядке? Это уродливый первый шаг, но решительно рассмотрите новый столбец для хранения этого значения для целей сортировки.

ORDER BY REPLACE(REPLACE([title],'the',''), 'a', '')

По общему признанию, это становится уродливым, когда вы закончите с этим:

REPLACE(REPLACE(REPLACE(REPLACE([title],'The ',''),'a',''),'of',''),'by','')

Ответ 5

Вы можете создать таблицу, поддерживающую полнотекстовый поиск (используя FTS модуль) в заголовке. Затем вы сможете быстро выполнять поиск по любым словам в названии, не требуя при этом дополнительной дополнительной работы с вашей стороны. Например, пользовательский запрос good bad ugly может создать "Хорошее, плохое и угрюмое" как один из его первых результатов. Дополнительная стоимость всего этого составляет около четверти длины самого текста в целом, но может быть больше для вашего набора данных, поскольку заголовки не являются полным текстом на английском языке. Вам также нужно потратить время на создание дополнительных индексов - вы не хотите создавать их на своем основном наборе данных в живой системе (очевидно), но это не должно быть слишком большой проблемой.

Ответ 6

Создайте виртуальный столбец (результат функции, который может быть реализован на С#) и выполните сортировку по этому виртуальному столбцу. Функция может перемещать "The" до конца, как в "Three Musketeers, The", или отбрасывать "The", что бы вы ни хотели сделать.