Month: October 2021

bookmark_borderSQL: Delete all Rows with “foo” in the String

Posted on by Tony

Let’s say you’re building something cool and you try to share this amazing thing with a friend. Then that friend decides to blow your DB up with useless entries. Because that is what friends do.

In such a scenario, you might end up with something like this:

select count(*) from table;
+----------+
| count(*) |
+----------+
|    12687 |
+----------+

If this is you, you might find yourself a bit distraught. But that’s ok, life is not that bad. Hopefully your friend did something like this:

|   12674 | foofoo12664      | foofoo12664@gmail.com        | foofoo12664     | $2y$10$102qeaPYWOzdIrXX2xNTIeOfEjHDKPW9xSRpByRTXas1MgkF7SOby | NULL    |
|   12675 | foofoo12665      | foofoo12665@gmail.com        | foofoo12665     | $2y$10$rcwXM0qSM20OrY8RQIQtU.ztcy5B2kVlT1C09R1ugJudxDBWw.7BK | NULL    |
|   12676 | foofoo12666      | foofoo12666@gmail.com        | foofoo12666     | $2y$10$kCzO15VoVs.l5F2112WY8eAyByLz9Bm1AC/bklQRE3pddWEEjWqHm | NULL    |
|   12677 | foofoo12667      | foofoo12667@gmail.com        | foofoo12667     | $2y$10$lVZVuNIhAwVPTmuJpyyUoO1..HQJhcFmqiGYVBdMCYlLIip4m8xr. | NULL    |
|   12678 | foofoo12668      | foofoo12668@gmail.com        | foofoo12668     | $2y$10$X8RplBFJpeShCg0qnsy64ecloXAtRfotgymi2WR709bTZ6.uN4kem | NULL    |
|   12679 | foofoo12669      | foofoo12669@gmail.com        | foofoo12669     | $2y$10$LVCjD9fqOVXi6A2LVX5FMOSTWnwq5WUK959Kw/M1QeTUsO3qaTBcm | NULL    |
|   12680 | foofoo12670      | foofoo12670@gmail.com        | foofoo12670     | $2y$10$smcLn6eirBkKeZayWVP4mONcoRfEWJOwYRbK08LndCO4F2hJ2Xj.G | NULL    |
|   12681 | foofoo12671      | foofoo12671@gmail.com        | foofoo12671     | $2y$10$mY11LeuTrz4sPLRov2nJceY974TDUIzqzO5spzTkKUDJowMpyszkW | NULL    |
|   12682 | foofoo12672      | foofoo12672@gmail.com        | foofoo12672     | $2y$10$fZJEkthgTXsWHRVYBsQMHuqJFn6FFIHg1vqibm8hqVH4f0yAbuHk2 | NULL    |
|   12683 | foofoo12673      | foofoo12673@gmail.com        | foofoo12673     | $2y$10$1/swwPhmHy66Oole.6Fepeh4hxJDwlOroMlDtCkgggxnsQsjAC86. | NULL    |
|   12684 | foofoo12674      | foofoo12674@gmail.com        | foofoo12674     | $2y$10$Whrwt8j19R4jFC6KdjGLoupsG78mH2fTzXIR78.lfQ/6xtZ/mNc3u | NULL    |
|   12685 | foofoo12675      | foofoo12675@gmail.com        | foofoo12675     | $2y$10$U0o9q3xWAOD2q/L2MBWn/OH4M5mJPODuk07fnfGIbTeKgaLDbBnka | NULL

In this scenario you can see common each row has similarities (i.e., “foofoo”). Because of this you can open your terminal and use the “Delete” command like this:

DELETE FROM table WHERE usersUid LIKE 'foofoo%';

This will remove each record in the table. This is what you need to know of the command above:

tablethe table storing the data;
usersUidthe column you are going to query;
%his operator is the wildcard, in my scenario I wanted it to delete every row that started with “foofoo” because that was the syntax they used. If I used the % operator before the query words, it would look for that syntax anywhere in the string.

You can then buy your friend a bag of coal for the holidays.


Cheers.