MySQL to Create an HTML Hyperlink from an URL

SELECT url,href FROM tbl_password where private = '1' and url NOT like '%squid%';

update tbl_password
set href=concat('< a href="',url,'">',url,'< /a>')
where private = '1' and url NOT like '%squid%';

I really wanted to strip the http:// off the anchor text as well

update tbl_password
set href=concat('< a href="',url,'">',SUBSTRING(url,7),'< /a>')
where private = '1' and url NOT like '%squid%' ;

Note here how flexible concat is with multiple parameters

Updating Data Strings with Concat

I needed to prefix http:// before any urls that didn't already have it.


update tbl_password set url=concat('http://',url) where url not like 'http://%';

(the following is my test statement which I used to avoid the risk of destroying data, by limiting it to one record)

update tbl_password set url=concat('http://',url) where url not like 'http://%' and url like '%supanames%';

Alternatively limit change to one record with 'limit'

update tbl_password set url=concat('http://',url) where url not like
'http://%' limit 1;