Converting URL to Local File Path in Batch MySQL INSERT Query Using SUBSTRING_INDEX() and CONCAT() at The Blog of Angelo

Converting URL to Local File Path in Batch MySQL INSERT Query Using SUBSTRING_INDEX() and CONCAT()

January 5th, 2009

I wanted to quickly take a URL (e.g. http://www.compiledweekly.com/somefolder/somefile.ext) and translate it to the local path (e.g. /home/user/public_html/somefolder/somefile.ext) while inserting multiple records into a new table. My first thought was to select all the records, use PHP to trim off the path, then insert the new record in the new table. There’s a better answer, use SUBSTRING_INDEX() and CONCAT() with a INSERT INTO table SELECT statement.

INSERT INTO new_table
SELECT CONCAT(‘/home/user/public_html/somefolder/’, SUBSTRING_INDEX(s.url, ‘/’, -1)) AS local_path
FROM source_table AS s
WHERE …

It does the job with out having to write a single line of PHP code!