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

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!

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

  1. Pingback: WALTER
  2. Pingback: white

Leave a Reply

Your email address will not be published. Required fields are marked *