Update MySQL (using regexp?)

I would like to update values which look like "someSubdomainInfo.mydomain.com" to "mydomain.com" (the .com is just an example, I'd like to catch everything else - basically clean the subdomain info)

Thought of using ^([a-z].*.)?([a-zA-开发者_如何学运维Z0-9_-].[a-z]) as RegExp (missing domain that start with a digit, possibly others)

Any ideas on how to write the relevant update statement?

Thanks,

Edit: looking at my question again, seems I did not present it clearly. I want this done to all domains, not on a one-by-one domain. In other words, I don't know what "mydomain.com" is ahead of time.

If I export to a csv I can use this regex and substitute with \2 (verified on EditPro text editor), and I'm looking for something similar to be dome directly in MySQL.

---

SUBSTRING_INDEX() is best suited for extracting portions of URLs.

If you know the domain extension, you can be clever and prevent accidental updates. Otherwise you could use a regex in the clause, or count the number of .s to prevent this update from altering the URLs you don't want to clean of their subdomain.

UPDATE test SET url = SUBSTRING_INDEX(url, '.', -2) WHERE SUBSTRING_INDEX(url, '.', -2) = 'mydomain.com';

---

RegExp are still primative in MySQL. They are good for matching and comparison, but not so much replacement.

My suggestion would be to use String Functions.

SELECT url_column, SUBSTRING(url_column, INSTR(url_column, '.mydomain.com') + 1)) FROM your_table;

This will look for the root domain in the column value. If it finds it, it will take just this portion of the string. Otherwise, it will keep the original string. Test it with the SELECT statement above and make it an UPDATE once you've adjust it for your schema.