I have multiple products on my website that have hardcoded links to other products in the product description (text). Those links are mixed case and are causing issues with duplicate pages on the Google index.

I would like to convert all URL's that contain /products/Name-Of-Product.html to /products/name-of.product.html using a MySql query.

Example:
Column 1: "Lorem ipsum text https://www.website.com/products/Name-OF-Product1.html text text Dolor Sit Amet"
Column 2: "LOREM Other ipsum text https://www.website.com/products/Name-OF-PRODUCT2.html text text DOLOR Sit Amet"

I would like to get to this end result:
Column 1: "Lorem ipsum text https://www.website.com/products/name-of-product1.html text text Dolor Sit Amet"
Column 2: "LOREM Other ipsum text https://www.website.com/products/name-of-product2.html text text DOLOR Sit Amet"

So i would need a query that searches in the text of the product descriptions for any links that contain /products/ and convert them to lowercase.

This is an example query i used to replace some text in the product descriptions, but that was "static":
UPDATE `isc_products` set `proddesc` = replace(`proddesc`,'</table><hr /><h3>Tip','</table></div><hr /><h3>Tip') WHERE `prodcode` REGEXP 'CH'.
I am currently trying to edit text that is different from product to product.

With my limited knowledge, i wasn't able to solve this problem. I would appreciate if someone could give me some suggestions.

Thank you!

CodePudding user response：

UPDATE your_table_name 
SET your_column_of_urls = LOWER(your_column_of_urls)
WHERE your_column_of_urls LIKE '%/product/%'

If I understand your question, you need a sql query for your case, the query above should be able to do the trick. LOWER() will change the values of the column to lower case.

Refer to this - Update all values of a column to lowercase

CodePudding user response：

For one URL per text and MySQL 8 :

WITH cte AS (
  SELECT "Lorem ipsum text https://www.website.com/products/Name-OF-Product1.html text text Dolor Sit Amet" txt
  UNION ALL
  SELECT "LOREM Other ipsum text https://www.website.com/products/Name-OF-PRODUCT2.html text text DOLOR Sit Amet"
  )
SELECT REGEXP_REPLACE(txt, 'https://[^ ]  |$', LOWER(REGEXP_SUBSTR(txt, 'https://[^ ]  |$')), 1, 1)
FROM cte

fiddle