Query optimization on INSERT ... SELECT ... SUBSTR

[Jess Mann]

Hello,

I have two tables, one which contains a listing of images (INT id, VARCHAR(50) image_path), and another which contains tags for those images (INT id, INT image_id, VARCHAR(20) tag).

Via pure SQL, I would like to take all the image_paths in my image table, and perform some string operations on them to create a tag based on the image name. I have the following SQL query, which does what I intend.

INSERT INTO table_tags ( image_id, tag ) SELECT id, REVERSE( SUBSTR( REVERSE(image_path), LOCATE( '.', REVERSE( image_path ) ) + 3, LOCATE( '/', REVERSE( image_path ) ) - LOCATE( '.', REVERSE( image_path ) ) - 1 ) ) AS tag FROM table_images

Each image_path is formatted as: /path/to/image/nameXX.ext
Such as: /var/www/images/bird09.jpg

However, the above query is monstrous. Typically, I would simply figure out the tag name via a scripting language like PHP, but for my own edification (and portability), I would like to do this via SQL.

I've looked through the various string operations available here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

My question is whether there is a simpler way to do the above (again, purely with SQL). It appears that the only regex matching functions available return a bool (rather than the matched string), that there is no function to search a string from the end backwards, and that there is no substring function which takes an ending index as a parameter.

Am I mistaken? Or, is there a better way to handle this?

Any suggestions, or references to alternative documentation would be greatly appreciated! Thanks,
-Jess

EDIT: I understand that this is a relatively simple question regarding string operations, but I'm trying to be more apt at using MySQL over relying on a scripting language for available operations. That said, even humoring me with other resources would be appreciated. ;) Thanks.



Edited 1 time(s). Last edit at 01/25/2008 12:34PM by Jess Mann.