ORDER BY name without leading "the" (and other complex code)

Question

I'm sorting song names from a SQLite database, and I'd like to sort ignoring any leading "The ". So, for example:

1  Labradors are Lovely
2  The Last Starfighter
3  Last Stop before Heaven

This answer solves this need in the simple case:

SELECT name FROM songs
ORDER BY
  CASE WHEN instr(lower(name),'the ')=1 THEN substr(name,5)
       ELSE name
  END
COLLATE NOCASE;

However, I'm already using a complex transformation on the name column. Combining the two I get this ugly, non-DRY code:

SELECT n, name
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 CASE WHEN instr(lower(name),'the ')=1 THEN
                   replace(
                     replace(
                       replace(
                         replace(
                           substr(name,5),
                           '.',''
                         ),
                         '(',''
                       ),
                       '''',''
                     ),
                     '  ',' '
                   )
                 ELSE
                   replace(
                     replace(
                       replace(
                         replace(name,'.',''),
                         '(',''
                       ),
                       '''',''
                     ),
                     '  ',' '
                   )
                 END
  END
COLLATE NOCASE;

Is there a way to use a variable or something during the query so that I can DRY up the code, and only have all that punctuation-replacement taking place in one location instead of two different case branches?

fyi `[0-9]*` matches all strings including the blank string. I think you want `[0-9]+` — Bohemian, Sep 18 '15 at 16:39
@Bohemian Not that I will have any blank song names, but this is not true in SQLite, where the [GLOB operator](http://www.tutorialspoint.com/sqlite/sqlite_glob_clause.htm) is not a regex. `[0-9]*` is "Starting at the front of the string, find a single digit, followed by zero or more of any character." It's the regex equivalent of `/^\d.*$/` — Phrogz, Sep 18 '15 at 16:56

score 2 · Accepted Answer · edited Sep 18 '15 at 17:29

Something like this should work.

SELECT n, name FROM (
  SELECT n, name,
  CASE WHEN instr(lower(name),'the ')=1 THEN substr(name,5)
       ELSE name
  END AS NameWithoutThe
  FROM songs
) AS inr
ORDER BY
  CASE WHEN name GLOB '[0-9]*' THEN 1
       ELSE 0
  END,
  CASE WHEN name GLOB '[0-9]*' THEN CAST(NameWithoutThe AS INT)
       ELSE
         replace(
          replace(
            replace(
              replace(
                NameWithoutThe,
                '.',''
              ),
              '(',''
            ),
            '''',''
          ),
          '  ',' '
         )
  END
COLLATE NOCASE;

Thanks! I like the solution of using a select to create a transformed value "variable" for later use. — Phrogz, Sep 18 '15 at 19:00

ORDER BY name without leading "the" (and other complex code)

1 Answers1