I had a problem with one column where corrupted data occurred. Column stored images in the base64 form. However, because of the programming error, it sometimes was also storing image description on image removal.
This article will demonstrate at this example usage of SQL’s NOT LIKE command.
Luckily I was also using soft removal in the same application. The unique record will be forever in the database but marked as removed.
Note: To prevent such issues is fundamental to practice TDD.
After patching application code, fixing database with SQL magic should be easy. All I needed to do is update rows in the image column, which did not contain correct records. Unfortunately, if the image was stored, it might not be stored (image description would be stored instead) and image data would be lost forever.
Instead of writing SQL regex (which are in a certain degree of their creator prone to error), I have decided to use simple NOT LIKE solution. So if the device record is removed and it does not contain image string in the required format, data cell should be blank.
Since everything behind
data:image/png;base64, is base64 representation of image we will use
% as replacing character.
This is a simple example of SQL command using NOT LIKE format:
SET image = ''
WHERE device.removed = TRUE
AND NOT (device.image LIKE 'data:image/png;base64,%');
Following example is the particular way how we update column using NOT LIKE command.