Search code examples
mysqlsortingalphabeticalalphabetized

MySQL sort by name

Is ir possible to sort a column alphabetically but ignoring certain words like e.g 'The'

e.g.

A normal query would return

string 1
string 3
string 4
the string 2

I would like to return 

string 1
the string 2
string 3
string 4

Is this possible?

EDIT Please note I am looking to replace multiple words like The, A, etc... Can this be done?

Solution

  • You can try

    SELECT id, text FROM table ORDER BY TRIM(REPLACE(LOWER(text), 'the ', ''))

    but note that it will be very slow for large datasets as it has to recompute the new string for every row.

    IMO you're better off with a separate column with an index on it.

    For multiple stopwords just keep nesting REPLACE calls. :)