The Background:

• items have a system code
• all system codes begin with the same string of text eg S-
• existing system codes have zeros to pad out the string so that all codes are of equal length eg S-001, S-011
• users need to be able to search for system codes
• using Sphinx and the awesome ThinkingSphinx gem

The Problem:

• the business wanted to ditch the unnecessary zeros in the system codes to match the format of other codes in the system eg X-1, C-20, S-14
• a search results table needed to be sortable by the system codes column eg S-1, S-2, S-10 instead of S-1, S-10, S-2 (the latter is what you get since it is an alpha sort on the whole string)
• I flippantly proclaimed that we were smart enough to sort the system codes correctly and could easily ditch the redundant zeros

The Options:

1. eat my words and keep the system codes as they are eg S-01, S-02, S-10
   • it would work but I don’t like letting the computer win
2. as above but use a view helper to hide the zeros used for padding
   • not acceptable since it would display system codes as S-1 and users would need to search for S-01
3. remove the prefix from the system codes, change them to integers and use a view helper to add back the prefix
   • not acceptable since the users need to search on the system codes eg S-1 instead of just 1
4. or somehow get Sphinx and ThinkingSphinx to sort them properly
   • Sphinx can’t handle a natural sort but maybe there is a hack

The Hack:

has "CAST(MID(systems.code, 3, LENGTH(systems.code) - 2) AS UNSIGNED)", :type => :integer, :as => :system_code_sort

Yes, it is only a “pseudo” natural sort as it doesn’t sort the alpha characters but in my specific case it did the job and solved the specific need.