![]() Most of the overhead will be at CPU time and memory. If you have to sort data excluding some symbols and you are not allowed to change your DB, you will get a slower selection because of filtering undesired values. The second solution (when only application can be modified): So on solumntForSorting you will get something like this: 0Im a Rainbow Too and 1911 is a Joke. Then on your application before inserting, concatenate your second condition ("With leading-digits at the end, by integer value.") as 0 or 1 to song name which first 'was cleaned' from undesired symbols. The first solution (when DB and application can be modified):Īdd to your table single column e.g. I can fix this particular test set with manual replaces for each undesired character: SELECT n Insert into songs (n,name) values (16,'P.O.D.-Boom') Insert into songs (n,name) values (11,'Labradors are Lovely') Insert into songs (n,name) values (5,'Ichthyosaurs Are Awesome') Insert into songs (n,name) values (23,'911 is a Joke') Insert into songs (n,name) values (10,'L.A. Insert into songs (n,name) values (2,'(Don''t Fear) The Reaper') Insert into songs (n,name) values (19,'The Promise') Insert into songs (n,name) values (15,'Pluto') ![]() Insert into songs (n,name) values (12,'L''accord parfait') Insert into songs (n,name) values (22,'99 Luftballons') Insert into songs (n,name) values (4,'i do, i do, i do') Insert into songs (n,name) values (3,'I Before E Except After C') ![]() Insert into songs (n,name) values (14,'Last Stop before Heaven') Insert into songs (n,name) values (8,'Imagine') Insert into songs (n,name) values (17,'Poles Apart') Insert into songs (n,name) values (1,'any old wind that blows') Insert into songs (n,name) values (13,'Lagan') Insert into songs (n,name) values (18,'Pressure') Insert into songs (n,name) values (21,'5 Years') Insert into songs (n,name) values (7,'I''m a Rainbow Too') Insert into songs (n,name) values (20,'Pule') Insert into songs (n,name) values (9,'La Moldau') Insert into songs (n,name) values (24,'1969') Insert into songs (n,name) values (6,'I''ll Be That Girl') (No, you cannot order by n ) create table songs (n integer, name text) The value 'n' should ideally come out in order. Is there a way to do this other than ~32 calls to replace()? However, I can't figure out how to replace every character (other than letters, numbers, and spaces) other than to call replace() for each character. parentheses, periods, hyphens, apostrophes) With leading-digits at the end, by integer value.** in the middle of text in numeric order.I am sorting songs in SQLite (on Android). ** Implement a collating sequence that sorts (unsigned) integers embedded ![]() ** May you share freely, never taking more than you give. ** May you find forgiveness for yourself and forgive others. ** The author disclaims copyright to this source code. Indexes, or on the columns of your table definitions. To use it, just add "COLLATE mixed" after the ORDER BY clause terms that That you compiled in the working directory and enter. To load the extension from the put the shared library To compile following the instructions on compiling loadable extensions ![]() I'm particularly interested in suggests for I hereby toss the code over the wall to the community for testing,Ĭriticism, and discussion. That implements a "MIXED" collating sequence that does what you want, I The code below implements a run-time loadable extension for SQLite ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |