Hello,
I am trying to remove some rows in a table, which are including a phrase at a defined column but i could not find the unique result for this.
What I wish to do is to remove all lines including http://xx.yy at link column
+----+----------+-----------+----------+--------------------------------------------------------+---------+
| id | userid | statusid | name | link | notes |
+----+----------+-----------+----------+--------------------------------------------------------+---------+
| 1 | 1 | 1 | sql-TEST | ["http:\/\/google.com","http:\/\/xx.aa.bb"] | test1 |
| 2 | 1 | 1 | sql-TEST | ["http:\/\/xx.yy.zz","http:\/\/google.fr"] | test2 |
| 3 | 1 | 2 | sql-TEST | ["http:\/\/xx.aa.bb","http:\/\/xx.aa.bb"] | test3 |
| 4 | 1 | 2 | sql-TEST | ["http:\/\/nbc.aa.bb","http:\/\/xx.yy.bb"] | test4 |
| 5 | 1 | 2 | sql-TEST | ["http:\/\/tt.aa.bb","http:\/\/xx.aa.bb"] | test5 |
| 6 | 1 | 2 | sql-TEST | ["http:\/\/xx.aa.bb","http:\/\/xx.yy.bb"] | test6 |
| 7 | 1 | 2 | sql-TEST | ["http:\/\/www.cnn.com"] | test7 |
+----+----------+-----------+----------+--------------------------------------------------------+---------+
Table name is mytable,
I tried below command but no joy:
delete from mytable where link like '%["http:\/\/xx.yy%' ;
Maybe as datas in related cells were seperated by "," it is not functioning..
Expected result should be:
+----+----------+-----------+----------+--------------------------------------------------------+---------+
| id | userid | statusid | name | link | notes |
+----+----------+-----------+----------+--------------------------------------------------------+---------+
| 1 | 1 | 1 | sql-TEST | ["http:\/\/google.com","http:\/\/xx.aa.bb"] | test1 |
| 2 | 1 | 1 | sql-TEST | ["http:\/\/google.fr"] | test2 |
| 3 | 1 | 2 | sql-TEST | ["http:\/\/xx.aa.bb"] | test3 |
| 4 | 1 | 2 | sql-TEST | ["http:\/\/nbc.aa.bb"] | test4 |
| 5 | 1 | 2 | sql-TEST | ["http:\/\/tt.aa.bb","http:\/\/xx.aa.bb"] | test5 |
| 6 | 1 | 2 | sql-TEST | ["http:\/\/xx.aa.bb"] | test6 |
| 7 | 1 | 2 | sql-TEST | ["http:\/\/www.cnn.com"] | test7 |
+----+----------+-----------+----------+--------------------------------------------------------+---------+
Thanks in advance
Boris