-1

I have a record in the column named websites with the following text:

["https://twitter.com/use312", "https://youtube.com/bel", "http://keepthiswebsite"]

I would like to delete all the websites that contain youtube.com and twitter.com, namely "https://twitter.com/use312" and "https://youtube.com/bel" , but keep all other substrings ("http://keepthiswebsite")

So the result of mysql statement on the record above should be ["http://keepthiswebsite"].

I would really like to do this with mysql, without python or some other scripting language. How can I do this?

sanjihan
  • 5,592
  • 11
  • 54
  • 119

1 Answers1

1

Here is what you looking for, but it is not very nice and you must hast a unique id on each row.

SELECT url ,CONCAT('[', result_url,']') as result_url
FROM (
SELECT u.url,
@r:= TRIM(BOTH ' ' FROM REPLACE(REPLACE(REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(CONCAT(url,',,,,,,,,,,'), ',', ids.id)),',',1)),'[',''),']','')),
@r:= IF(@r = '' , NULL, @r),
@r:= IF(INSTR( @r ,'youtube.com') > 0 , NULL, @r) ,
@r:= IF(INSTR( @r ,'twitter.com') > 0 , NULL, @r) ,
-- Here you can add your own removes. only duplicate last line
GROUP_CONCAT( @r SEPARATOR ', ' )  as result_url
FROM (
SELECT 1 as id UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 ) as ids
CROSS JOIN `urlstable` u
CROSS JOIN ( SELECT @r:='') as init
GROUP BY u.id
ORDER BY u.id,ids.id
) as remove;

sample

mysql> SELECT * FROM urlstable;
+----+--------------------------------------------------------------------------------------+
| id | url                                                                                  |
+----+--------------------------------------------------------------------------------------+
|  1 | ["https://twitter.com/use312", "https://youtube.com/bel", "http://keepthiswebsite"]  |
|  2 | ["https://twitterr.com/use312", "https://youtube.com/bel", "http://keepthiswebsite"] |
|  3 | ["https://twitterr.com/use312", "https://google.com/bel", "http://keepthiswebsite"]  |
|  4 | ["https://mydomain.de/use312", "https://youtube.com/bel", "http://keepthiswebsite"]  |
+----+--------------------------------------------------------------------------------------+
4 rows in set (0,00 sec)

mysql> SELECT url ,CONCAT('[', result_url,']') as result_url
    -> FROM (
    -> SELECT u.url,
    -> @r:= TRIM(BOTH ' ' FROM REPLACE(REPLACE(REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(CONCAT(url,',,,,,,,,,,'), ',', ids.id)),',',1)),'[',''),']','')),
    -> @r:= IF(@r = '' , NULL, @r),
    -> @r:= IF(INSTR( @r ,'youtube.com') > 0 , NULL, @r) ,
    -> @r:= IF(INSTR( @r ,'twitter.com') > 0 , NULL, @r) ,
    -> -- Here you can add your own removes. only duplicate last line
    -> GROUP_CONCAT( @r SEPARATOR ', ' )  as result_url
    -> FROM (
    -> SELECT 1 as id UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    ->  UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 ) as ids
    -> CROSS JOIN `urlstable` u
    -> CROSS JOIN ( SELECT @r:='') as init
    -> GROUP BY u.id
    -> ORDER BY u.id,ids.id
    -> ) as remove;
+--------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
| url                                                                                  | result_url                                                                          |
+--------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
| ["https://twitter.com/use312", "https://youtube.com/bel", "http://keepthiswebsite"]  | ["http://keepthiswebsite"]                                                          |
| ["https://twitterr.com/use312", "https://youtube.com/bel", "http://keepthiswebsite"] | ["https://twitterr.com/use312", "http://keepthiswebsite"]                           |
| ["https://twitterr.com/use312", "https://google.com/bel", "http://keepthiswebsite"]  | ["https://twitterr.com/use312", "https://google.com/bel", "http://keepthiswebsite"] |
| ["https://mydomain.de/use312", "https://youtube.com/bel", "http://keepthiswebsite"]  | ["https://mydomain.de/use312", "http://keepthiswebsite"]                            |
+--------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
4 rows in set (0,00 sec)

mysql>
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39