CODEWITHSUNDEEP
mysqlmariadb
David Frickert
David Frickert

Reputation: 127

How to find records with a similar string (regex)?

I have some problems in my database. I have some http:// and some https:// links. I need to switch all http:// to https://, but there's a small problem, some links are duplicates (have both versions) so if I attempt to change, I will get duplicate error.

How can I find those? I have thought in using regex [.]com\/[a-zA-Z]* which will trim for example http://www.somewebsite.com/abc.gif to .com/abc (implying all are .com of course, if not it won't work).

Can someone help me build an SQL command for that? I tried using mariadb REGEXPR_SUBSTR but I didnt get anywhere because it would compare every single row with all the rows, taking infinity.

Thanks!

Upvotes: 0

Views: 48

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You ca easy do it in 2 Steps:

1) Replace all http: to https: with UPDATE IGNORE

UPDATE IGNORE yourTable
set yourField = replace(yourField, 'http:', 'https:')
WHERE yourField LIKE 'http:%';

2) SELECT OR DELETE all ROWS start with http:

SELECT yourTable WHERE yourField LIKE 'http:%';

Upvotes: 3

Related Questions