CODEWITHSUNDEEP
mysql
alexanoid
alexanoid

Reputation: 25780

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.

Upvotes: 0

Views: 710

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

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 !

Upvotes: 2

Related Questions