MYSQL Select on Trim

Question

Really simple, I want to select all titles that starts with letter 'A' while ignoring the dash at the beginning of the string.

SELECT * TRIM(LEADING '- ' FROM title) WHERE title LIKE 'A%'

This just doesn't seem to work. Please help. Thanks.

Answer 1

SELECT TRIM(LEADING '- ' FROM title) FROM [missing table here] WHERE TRIM(LEADING '- ' FROM title) LIKE 'A%'

Answer 2

If you want decent speed, you should avoid per-row functions as much as possible. Normally, I'd suggest an insert/update trigger to store modified data so as to amortise the cost of the calculation across all selects (see here for an explanation along with some other guidelines I follow) but, for this particular case, there's an easier way ^*a.

Just use:

select * from table where title like 'A%' or title like '- A%'

If the execution engine of your DBMS is any good, it will turn that into two very fast (assuming title is indexed) passes of the data without having to process every single row in the table.

If your execution engine is not that smart, try:

select * from table where title like 'A%'
union all select * from table where title like '- A%'

to see if that helps. Make sure you use union all, not just union. The latter will attempt to remove duplicates, unnecessary in this case since a title cannot start with both "A" and "- A".

And, as with all optimisations, measure, don't guess!

---

^*a You may still want to consider using the trigger/extra-column method if there's a chance you want a case-insensitive search. The method in this answer will work okay for case-insensitivity on the first letter (a%/A%/-a%/-A%) but will quickly degrade if you're looking for something like items starting with Bill which would require 32 separate clauses (2⁴ character combinations with and without the hyphen prefix).

Answer 3

SELECT * TRIM(LEADING '- ' FROM title) WHERE title LIKE 'A%'

Your where clause will never work, as you're trying to compare to the POST-trim data, but the database is doing the comparison on pre-TRIM.

There's two choices:

SELECT *
FROM table
WHERE title LIKE '- A%'

or

SELECT *, TRIM(LEADING '- ' FROM title) AS title
FROM table
WHERE title LIKE 'A%'

Of the two, the first one is preferable, as it allows indexes to be used. The second one, since you're comparing against dynamically derived data, will not.