CODEWITHSUNDEEP
phpmysqlsql
Drew
Drew

Reputation: 6862

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

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:

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.

Upvotes: 1

Views: 1043

Answers (2)

dash
dash

Reputation: 91480

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

Read more about both functions here

Upvotes: 3

Vitor Furlanetti
Vitor Furlanetti

Reputation: 451

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


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

Upvotes: 1

Related Questions