How to select the substring of a filename in MySQL?

Question

I'm doing an autocomplete to find files matching a search parameter entered in a form. If someone enters "p" it returns ALL pdf files, which I don't want. I only want them to search the name of the file. Here's my query...

SELECT uid, link_text 
FROM tblProfile 
WHERE link_text LIKE "%'. $text . '%"
ORDER BY link_text
LIMIT 8

I tried to do...

SELECT SUBSTRING(link_text,-4,4)
FROM tblProfile
WHERE SUBSTRING(link_text,-4,4) like "%'. $text . '%"

But it didn't work. I could create a new column in the table to store the name w/out the extension but there has to be a better way!

Answer 1

Try with SUBSTRING_INDEX().

Example: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index