alexanoid alexanoid - 11 days ago 6
MySQL Question

Remove html link from mysql database field with SQL

I have an old MySQL database and I need to remove different HTML links from

description
field.

So, for example after SQL execution on
description
with a following text:

Hello <a href="http://www.example1.com">test</a> World !


I need to get:

Hello World !


Please note that in my database HTML links are not the same and contain different addresses and texts.

Is it possible to do with MySQL SQL query and if so, could you please provide an example.

Answer

You can use a query like this. You only must change FIELDNAME to your fieldname and TABLENAME to your tablename. In your Sample there are one space behind HELLO ** and one before ** WORLD, so you have 2 spaces in the RESULT

SELECT
CONCAT(
    SUBSTR(FIELDNAME,1,
    INSTR(FIELDNAME,'<a href=')-1)
,
    SUBSTR(FIELDNAME,
    INSTR(FIELDNAME,'</a>')+4)
)
FROM YOURTABLE;

sample

SELECT 
CONCAT(
    SUBSTR('Hello <a href="http://www.example1.com">test</a> World !',1,
    INSTR('Hello <a href="http://www.example1.com">test</a> World !','<a href=')-1)
,
    SUBSTR('Hello <a href="http://www.example1.com">test</a> World !',
    INSTR('Hello <a href="http://www.example1.com">test</a> World !','</a>')+4)
);

result

Hello  World !