PEDIDO POR caracteres alfanuméricos solo en SQLite

Estoy orderando canciones en SQLite (en Android). Quiero pedirlos:

  1. Sin distinción de mayúsculas y minúsculas
  2. Con dígitos iniciales al final, por valor integer.
  3. Sin puntuación (por ejemplo, paréntesis, puntos, guiones, apóstrofes)

Tengo 1 y 2 trabajando (ver a continuación) . Sin embargo, no puedo encontrar la forma de replace cada carácter (que no sean letras, numbers y espacios) que no sea para llamar a replace() para cada carácter.

¿Hay alguna manera de hacer esto que no sea ~ 32 llamadas para replace() ?
(Valores ASCII 33-47,58-64,91-96,123-126)


Aquí hay una tabla de testing. El valor 'n' idealmente debería salir en order. (No, no puedes orderar por 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''ma 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,'LA 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,'POD-Boom'); 

Aquí está la solución para solo 1 y 2 arriba:

 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 

Para este set de testing produce resultados en este 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

Puedo arreglar este set de testing en particular con reemploops manuales para cada carácter no deseado:

 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 

Agregaría una columna adicional en la tabla, llamada "SortingName" o algo así. Calcule este valor al insert, idealmente no en SQL, sino en un lenguaje de nivel superior donde tenga todas estas agradables operaciones de string.

Realmente no entendí esto con el número. Supongo que lo más simple que puedes hacer es extraer el número antes de insert y ponerlo en otra columna, como "SortingNumber".

Luego simplemente ordera de esta manera:

 Order By SortingName, SortingNumber 

(O al revés.)

Otra ventaja es el performance. Por lo general, lees datos mucho más a menudo de lo que escribes. Incluso puede crear índices en estas dos columnas de sorting, lo que generalmente no es posible si lo calcula en la consulta.

En mi opinión, el enfoque de mayor performance es crear un activador para llenar un nuevo campo llamado sort_key . Necesitará una key principal.

 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 

Tenga en count que este enfoque es amigable con el índice , puede crear un índice sobre una nueva columna para evitar operaciones de exploración completa de tabla.

La primera solución (cuando DB y la aplicación se pueden modificar):

Agregue a su tabla una sola columna, por ejemplo, solumntForSorting. Luego, en su aplicación antes de insert, concatenar su segunda condición ("Con dígitos iniciales al final, por valor integer") como 0 o 1 al nombre de la canción que primero se "limpió" de símbolos no deseados. Entonces en SolumntForSorting obtendrás algo como esto: 0Im a Rainbow Too y 1911 es una Broma .

La segunda solución (cuando solo la aplicación puede ser modificada):

Si tiene que orderar datos excluyendo algunos símbolos y no tiene permitido cambiar su DB, obtendrá una selección más lenta debido al filtrado de valores no deseados. La mayor parte de la sobrecarga será en time de CPU y memory.

El uso de la function de reemploop es tedioso desde mi punto de vista, por eso sugiero usar CTE con la list de valores que desea descartar, como este ('.', '.', ';', '(', ')', '' '', '-'). CTE será voluminoso como reemploop múltiple pero es más fácil de modificar y mantener.

Prueba esta solución:

  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 Sn, 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 

He probado en sqlfiddle.

En la list ('28', '29', '2C', '2E', '27') se tienen en count los valores de los códigos ASCII (en hexadecimal) de los que desea escapingse.

También puede intentar usar valores como: ('.', '.', ';', '(', ')', '''', '-') .

 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 Sn, 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 

Para que esta sorting funcione de forma rápida y simple, debe poder cambiar su database y su aplicación.

Si tiene permiso para crear funciones, esto es lo que crearía (tomado de Cómo quitar todos los caracteres no alfabéticos de una cadena en SQL Server y modificado un poco):

 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 

Esto cumpliría con su requisito n. ° 3 y eliminaría toda la basura de su cadena, luego su consulta se vería así:

 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 

No se ve bonito y podría no tener el mejor performance. Probablemente lo haría, lo que sugirió Stefan. Analice los nombres de sus canciones e inserte los recortados en una columna separada solo para orderar (y, por supuesto, tenga un índice en esa columna). Debería ser la mejor solución.

Puede usar los Enlaces de NDK de Android sqlite3 para get acceso a la API sqlite3 c completa mediante el uso de llamadas JNI.

Luego puede definir nuevas secuencias de intercalación utilizando sqlite3_create_collation_v2() y funciones relacionadas.

Este enfoque no cambia la database, ya que la intercalación solo se reemplaza en la connection de la database actual. Por lo tanto, cumple ese requisito porque funciona si la database es de solo lectura.

Observe que digo que puede. ¡No estoy diciendo que DEBERÍAS! Sopese los pros y los contras de este enfoque, ya que en la mayoría de los casos probablemente no valga la pena el esfuerzo adicional.