SQL find and replace text inside column

Question

I am trying to remove all links from column_content in my table_name. Links look like:

<a style="text-decoration:none" href="/index.php?o=oda-res-site">.</a>

and href="........" is different in each record.

Can you help with a statement with wildcards that will clean all links <a ..../a> ?

I tried this:

UPDATE MyTable
SET StringColumn = REPLACE (StringColumn, 'SearchForThis', 'ReplaceWithThis')

but I could not found the proper wildcards to include -if possible- all links.

Thank you I use phpMyadmin

What database management program are you using? add a tag for `sql-server`, `MySQL` or whatever it is. — Smandoli, Apr 15 '16 at 13:05
What sort of information is in a `column_content` record? Can there be multiple links? — Smandoli, Apr 15 '16 at 13:06
I use mysql and handle it with phpMyAdmin. Column type is:longtext so there can be other links that I do not want to remove. — geoplous, Apr 16 '16 at 04:52

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

If it's SQL Server you're using, REPLACE doesn't handle wildcards or regex so you have to loop using PATINDEX instead.

Regex pattern inside SQL Replace function?

edited May 23 '17 at 12:31

Community

1
1

answered Apr 15 '16 at 13:14

George Dando

444
2
11

score 0 · Answer 2 · edited Apr 15 '16 at 13:46

0

Hm... If I got you right, the solution shouldn't be that difficult. Following a sample code basing on your example:

DECLARE @MyString nvarchar(250) = N'<a style="text-decoration:none" href="/index.php?o=oda-res-site">.</a>';
SELECT @MyString as MyOldString, LEFT(@MyString, CHARINDEX('href="', @MyString)+5) + '">.</a>' as MyNewString

edited Apr 15 '16 at 13:46

Jaydip Jadhav

12,179
6
24
40

answered Apr 15 '16 at 13:35

Tyron78

4,117
2
17
32

Thanks for your try but it didn't work. I have basic knowledge in mysql, so I just copy paste the above at phpMyAdmin > sql tab. The error is: #1064 - You have an error in your SQL syntax; – geoplous Apr 16 '16 at 04:32
Oh, ok, if it's mysql, the query might require some modification. I tested it on Sqlserver. Guess you might need to use instring or similar instead of charindex. Im not that familiar with mysql. – Tyron78 Apr 16 '16 at 15:49

SQL find and replace text inside column

2 Answers2