Dennis Cote wrote: > Andrew Gatt wrote: > >> I have a table of music artist names which i'd like to output in order. >> Normally i just use: >> >> select * from artists order by artist_name; >> >> What i'd really like to do is order the artists by name but ignore any >> "the" or "the," preceding it. >> >> > > You could try something like this: > > select * from t > order by > case > when lower(substr(artist, 1, 3)) = 'the' then substr(artist, 4) > when lower(substr(artist, 1, 4)) = 'the,' then substr(artist, 5) > else artist > end; > > HTH > Dennis Cote > > Thanks for the help, however its giving me a compile error:
Error: wrong number of arguments to function substr() I'm using sqlite 3.3.6, but i'm presuming the two and three variable substr functions go back further than this? Doing tests it seems to be the two variable version it doesn't like. Do i need to upgrade my sqlite library? Thanks again Andrew _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
