substring query
I am looking for some guidance on creating a substring query. I have a column that stores a path to a file. I would like to extract that file extension and that is it and display it on my results. However, the paths are different lengths and some extensions are 3 letter and some are 4, eq 'html'. The only common they all have is the period before the extension. Anyone created a nested substring query that can do what I am looking to do? Thanks, Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: substring query
Thanks Guys for you insights. It may be a little more complicated then I made it out to be. I have tried this select substring_index(myfiled,'.',-2) from mytable. This has gotten me to a good starting point. But I still have two problems. After the extension there is a space and more wording. I want to cut that off. Also, some paths do not have an extension and I am trying to ignore those. So simply. I am just trying to pull out the file extension but there were some conditions I did not list. -Aaron SUBSTRING_INDEX should do what you want. SELECT SUBSTRING_INDEX('myfile.path','.',-1) = 'path' SELECT SUBSTRING_INDEX('myfile.pth','.',-1) = 'pth' or, in a version that's closer to real life usage: SELECT SUBSTRING_INDEX(myfield,'.',-1) from mytable http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: substring query
Sorry Jay, Here is what I came up with. select substring(substring_index(myfile,'.',-2),1,4) AS MyColumn from mydatabase group by MyColumn; That appears to yield what I need. I just need to filter out the results that do not have an extension. -Aaron On Thu, Jun 10, 2010 at 9:28 AM, Jay Blanchard jblanch...@pocket.com wrote: Thank you for that update, would have been good to have from the start. SELECT SUBSTRING_INDEX('my.doc','.',-1) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org