MySQL Query to Replace http:// and www in website field

Question

I have a column in my database for website URL and there are many different types of results. Some with www, some with http:// and some without.

I really need to clean up the field, so is there a query I can run to:

Replace all domains with just domain.com format. So remove any www or http://'s
If there is any fields with invalid format like "N/A" or something, so anything without a "." I need to empty it.

And then of course I will update my PHP code to automatically strip it from now on. But for the current entries I need to clean those up.

www.example.com and example.com are different hostnames, can resolve to different IPs, and can be handed by different virtual hosts (over HTTP) even if they resolve to the same IP. Assuming that they are the same is unwise. — Quentin
– Quentin, Commented Dec 7, 2011 at 20:36

dash · Accepted Answer · 2011-12-07 20:52:46Z

3

You can use the REPLACE function to achieve your first point - see the other answers for this. However, I would seriously consider leaving www in the entries as is; because, as the first comment points out, there are actual differences. You might also miss url's like www2.domain.com for example. If you wanted to display them in your app, you can simply remove them in the text presentation (by substringing after the first '.' for example) but leave the href consistent (if displayed as links).

Your second point can be achieved using the INSTR or LOCATE functions.

Simply:

UPDATE table SET url = 'N/A' WHERE LOCATE('.', url) = 0

Comments

Vitor Furlanetti · Accepted Answer · 2011-12-07 20:38:34Z

1

UPDATE table SET column = REPLACE(column, 'http://', '');


UPDATE table SET column = REPLACE(column, 'www.', '');

answered Dec 7, 2011 at 20:38

Vitor Furlanetti

4513 silver badges8 bronze badges

1 Comment

No Results Found Over a year ago

This would break the entry for something like http://no-www.org/

Collectives™ on Stack Overflow

MySQL Query to Replace http:// and www in website field

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related