I'm almost done transferring/reconstructing a substantial mysql db for new application. Column 'website' shows 'www.example.com'. Because the new application reads it as a hyperlink, I need the column to read 'http://www.example.com'. Is there a way to add the 'http://' in the beginning of each record for that column? Thanks in advance!
3 Answers
You can use the CONCAT function to do that:
UPDATE tbl SET website=CONCAT('http://', website);
If you want to get cleverer and only update columns which don't already have http:// prepended, try
UPDATE tbl SET website=CONCAT('http://', website)
WHERE website NOT LIKE 'http://%';
Update
To prevent update to columns that have no site in them currently use this
UPDATE tbl SET website=CONCAT('http://', website)
WHERE website NOT LIKE 'http://%' AND website<>'';
4 Comments
Gina Geick
Great, thanks! Except... Now I have http://.. nothing in rows that didn't have a URL LOL
David Passmore
Ahh. Sorry didn't think off that... use
UPDATE tbl SET website='' WHERE website = 'http://' to fix that :)Gina Geick
Hm. That took all the http://'s out again :) Couldn't I use something like UPDATE tbl SET website=CONCAT('http://', website) WHERE website NOT LIKE ' '; (as in empty)?
Gina Geick
Not allowed yet... I'm just a minion :P
You can use the concat command. Something like
SELECT CONCAT('http://', website) FROM table
Comments
Use concat to generate the new column:
UPDATE table1 SET website = CONCAT("http://", website);
