I ran into a problem with track numbers that have letters; I have a lot of vinyl rips that often use A1, or AA3, etc in the tracknumber. The CAST(tracknumber AS INTEGER) function worked great for the number only track numbers, but track numbers with letters and double digits sort like this, A1, A10, A11, A12...B1, B10, B11, B12, etc.
So, I changed my first CREATE TABLE to this and it works, but I was wondering if you know a better way:
DROP TABLE IF EXISTS MediaTable;
CREATE TABLE MediaTable AS
SELECT
path,
subsong,
genre,
multivaluegenre2jsonarray(genre) genresplit,
grouping,
[original album],
[original title],
[album artist],
album,
discnumber,
CASE
WHEN tracknumber LIKE '%0%' THEN substr(tracknumber, 1, instr(tracknumber, '0') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '0')))
WHEN tracknumber LIKE '%1%' THEN substr(tracknumber, 1, instr(tracknumber, '1') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '1')))
WHEN tracknumber LIKE '%2%' THEN substr(tracknumber, 1, instr(tracknumber, '2') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '2')))
WHEN tracknumber LIKE '%3%' THEN substr(tracknumber, 1, instr(tracknumber, '3') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '3')))
WHEN tracknumber LIKE '%4%' THEN substr(tracknumber, 1, instr(tracknumber, '4') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '4')))
WHEN tracknumber LIKE '%5%' THEN substr(tracknumber, 1, instr(tracknumber, '5') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '5')))
WHEN tracknumber LIKE '%6%' THEN substr(tracknumber, 1, instr(tracknumber, '6') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '6')))
WHEN tracknumber LIKE '%7%' THEN substr(tracknumber, 1, instr(tracknumber, '7') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '7')))
WHEN tracknumber LIKE '%8%' THEN substr(tracknumber, 1, instr(tracknumber, '8') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '8')))
WHEN tracknumber LIKE '%9%' THEN substr(tracknumber, 1, instr(tracknumber, '9') - 1) || printf('%02d', substr(tracknumber, instr(tracknumber, '9')))
ELSE CAST(tracknumber AS INTEGER)
END AS tracknumber_sorted
FROM MediaLibrary
WHERE genre LIKE '%Score%' OR genre LIKE '%Stage & Screen%';