ORDER BY буквенно-цифровыми символами только в SQLite

Ответ 1

Я бы добавил дополнительный столбец в таблице, называемый "SortingName" или что-то еще. Вычислите это значение при вставке, в идеале, не в SQL, а на языке более высокого уровня, где у вас есть все эти хорошие операции с строкой.

Я действительно не понимал эту вещь с номером. Я думаю, простейшая вещь, которую вы можете сделать, это извлечь номер перед вставкой и поместить его в другой столбец, например "SortingNumber".

Затем просто выполните следующие действия:

Order By
  SortingName,
  SortingNumber

(Или наоборот)

Другим преимуществом является производительность. Вы обычно чаще читаете данные, а затем пишете их. Вы даже можете создавать индексы в этих двух столбцах сортировки, что обычно невозможно, если вы вычисляете его в запросе.

Ответ 2

По моему мнению, подход с наивысшей производительностью - создать триггер для заполнения нового поля с именем 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

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

Ответ 3

Первое решение (когда DB и приложение могут быть изменены):

Добавьте к таблице один столбец, например. solumntForSorting. Затем в своем приложении перед вставкой соедините второе условие ( "С ведущими цифрами в конце, по целочисленному значению" ) в качестве 0 или 1 в название песни, которое сначала "было очищено" от нежелательных символов. Итак, на solumntForSorting вы получите что-то вроде этого: 0Im Rainbow Too и 1911 - шутка.

Второе решение (когда только приложение может быть изменено):

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

Использование функции замены является утомительным с моей точки зрения, поэтому я предлагаю использовать 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

Чтобы сделать эту сортировку быстрой и простой, вы должны иметь возможность изменять свою БД и приложение.

Ответ 4

Если вам разрешено создавать функции, это то, что я создал (взятый из Как удалить все неалфавитные символы из строки в 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

Это соответствовало бы вашему требованию №3 и удалило бы все ненужные файлы из вашей строки, тогда ваш запрос будет выглядеть следующим образом:

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

Это не выглядит красиво и может не иметь лучшей производительности. Я бы, наверное, сделал то, что предложил Стефан. Разбирайте имена своих песен и вставляйте обрезанные в отдельный столбец только для заказа (и, конечно, указатель на этот столбец). Это должно быть наилучшим решением.

Ответ 5

Вы можете использовать sqlite3 Android NDK Bindings, чтобы получить доступ к полному API sqlite3 c с помощью вызовов JNI.

Затем вы можете "Определить новые последовательности сортировки" с помощью sqlite3_create_collation_v2() и связанных функций.

Этот подход не изменяет базу данных, поскольку сортировка только переопределяется в текущем соединении с базой данных. Таким образом, он удовлетворяет этому требованию в том, что он работает, если база данных доступна только для чтения.

Заметьте, я говорю, что вы можете. Я не говорю, что СЛЕДУЕТ! Взвесьте плюсы и минусы этого подхода, так как в большинстве случаев это, вероятно, не стоит дополнительных усилий.