It should work, but it won’t be as fast as it should be. Even with an index on the column the cast will prevent it from using the index. If there are a lot of rows it will slow down. Can you change the column type, if not can you add another that is the integer of this one, and then index that column and sort on that.
I guess you have some “Casiers” as “B37” or whatever for such choice. The sad part of converting on-the-fly a value in SQLite is losing so much speed that it could have if the column was just numeric and indexed. You ended with an intense overhead of an entire sort of the involved expressions in this query. Since SQLite 3.9, SQLite (also) allows indexed expressions, if your current engine allows it, you should try it. Also, when involving mixing alpha and numeric data while ordering, things can get tricky, if this is your case, you probably would like a right aligned value approach.
Example:
DROP INDEX IF EXISTS idx_casier_order;
DROP TABLE IF EXISTS casier_table;
CREATE TABLE "casier_table" (
"casier" TEXT
);
CREATE INDEX idx_casier_order ON casier_table(SUBSTR(' ' || TRIM(casier), -10)); -- expression index
INSERT INTO "casier_table" VALUES ('7');
INSERT INTO "casier_table" VALUES ('1');
INSERT INTO "casier_table" VALUES ('5');
INSERT INTO "casier_table" VALUES ('3');
INSERT INTO "casier_table" VALUES ('36');
INSERT INTO "casier_table" VALUES ('789');
INSERT INTO "casier_table" VALUES ('72');
INSERT INTO "casier_table" VALUES ('aaa');
INSERT INTO "casier_table" VALUES ('nnn');
INSERT INTO "casier_table" VALUES ('bbb');
INSERT INTO "casier_table" VALUES ('c37');
INSERT INTO "casier_table" VALUES ('j23');
INSERT INTO "casier_table" VALUES ('f99');
-- 3 selects to try
-- Unordered
SELECT * FROM casier_table;
-- As Integer, errors got unordered as 0
SELECT * FROM casier_table ORDER BY CAST(casier AS Integer);
-- indexed expression right aligned with 10 places
SELECT * FROM casier_table ORDER BY SUBSTR(' ' || TRIM(casier), -10); -- must match the index
Casier (in this case) is a numeric value (1 thru I do not know, but low value). No alpha or anything else (0-9 only).
Yes, I stored that integer value as string. But because I display it - so I would have to convert it to String in the ListBox if I store it as INTEGER - I think the speed slow down due to CAST is… peanuts.
On a 575 Row Data Base with 13 Columns (First Name, Family Name, 3 SQLDates, City names…) the impact is very low. (excepted with my i5/2014 MacBook Pro, but it is a 9 years old computer, and running VirtualBox / Windows 10 does not accelerate things )
Also, the sort is always 1 at the top (who will seach an integer value Backward ?) and there is a “Search in the Display ListBox” feature available.
Nearly 25 years since REALbasic 1.0, and the documentation still not have goodies (code snippet that resolve computer real day problems).
)