Search code examples
mysqlsqltextreplacehref

SQL Query Replace Text column as Hyperlink <a href="">

I have a table Products

ID | Productname |      Product_url                     |
+----+-----------+--------------------------------------+
|  1 | ShirtXS   | txthttps://www.product.com/shirt-xs  |
|  2 | TrousersM | txthttps://www.trousers.org/tM-sizeM |

Product_url is VARCHAR(512)
How to write SQL query to format Product_url column text value to <a href="producttextvalue">Link</a> ?

Basically, all values from Product_url field should become links with same link labels.

https://www.product.com/shirt-xs to <a href="https://www.product.com/shirt-xs">Link</a>  
https://www.trousers.org/tM-sizeM to <a href="https://www.trousers.org/tM-sizeM">Link</a>  

ID   | Productname |  Poduct_url  |
+----+-------------+--------------+
|  1 | ShirtXS     |     Link     |
|  2 | TrousersM   |     Link     |
Solution

  • Basically you can use CONCAT() to add needed prefix and suffix around the URL, like:

    UPDATE
    `products`
SET
    `product_url` = CONCAT('<a href="', product_url, '">Link</a>');

    However, as I mentioned in my comment, better to keep the exact link only in your database and to format the HTML code within your front-end page.