Text manipulation of strings containing "The "

Question

I have a table in a MySQL database which contains data like this;

ID  text
1   Action Jackson
2   The impaler
3   The chubby conquistador
4   Cornholer

I want to display them in alphabetical order minus the leading "The ". This is what I've come up with which works.

SELECT ID, CASE LEFT(l.text, 4) WHEN "The " THEN CONCAT(RIGHT(l.text, LENGTH(l.text) - 4), ", The") ELSE l.text END AS "word"
FROM list l

This solution seems a little clunky, does anyone have a more elegant answer?

score 1 · Accepted Answer · answered Aug 09 '12 at 10:20

1

I think this is what you are looking for:

SELECT ID,
       text
FROM list l
ORDER BY TRIM(LEADING 'The ' FROM text);

answered Aug 09 '12 at 10:20

Omesh

27,801
6
42
51

score 1 · Answer 2 · answered Aug 09 '12 at 10:20

1

If you can at all, I would think of restructuring your data a bit.. Its hundreds of times better to rely on mysql indexes and proper sorting instead of doing it dynamically like this.

How about adding a field that drops the 'The ', and sort on that? You could make sure that this secondary field is always correct with a few triggers.

answered Aug 09 '12 at 10:20

Evert

93,428
18
118
189

having a whole table get bloated hence slow down just because you have a query that you want sorted ? I wouldn't go that way. I suppose this could work with other RDBMS's that can put certain columns of a table in another partition, but not with MySQL. – Tuncay Göncüoğlu Aug 09 '12 at 10:40
1

Denormalization is a very common approach to optimize query access. The accepted answer cannot every be indexed, so it will be impossible to use after the table gets bigger. – Evert Aug 09 '12 at 10:46
That makes sense, I'll have a look into the feasibility of changing the database. Thanks Evert! – Gregology Aug 09 '12 at 10:47

score 1 · Answer 3 · answered Aug 09 '12 at 10:20

1

SELECT TRIM(LEADING 'The' FROM text) as word 
FROM list 
ORDER BY TRIM(LEADING 'The' FROM text)

answered Aug 09 '12 at 10:20

Shakti Singh

84,385
21
134
153

Text manipulation of strings containing "The "

3 Answers3