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 was sometimes also storing image description on image removal.
This article will demonstrate the usage of SQL’s NOT LIKE command in this example.
Luckily I was also using soft removal in the same application. So 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 the application code, fixing the database with SQL magic should be easy. All I needed to do was update rows in the image column which did not contain correct records. Unfortunately, if the image was stored, it might not be stored in reality. Image description would be stored instead, and image data would be lost forever.
Instead of writing SQL regex (which is in a certain degree of their creator prone to error), I have decided to use a simple NOT LIKE SQL solution. So if the record is removed and does not contain an image string in the required format, the data cell should be blank.
Since everything behind data:image/png;base64, is base64 representation of image we will use % as replacing character.
Here follows is a simple example of an SQL command using the NOT LIKE format:
UPDATE app.device
SET image = ''
WHERE device.removed = TRUE
AND NOT (device.image LIKE 'data:image/png;base64,%');The following example is how we update columns using NOT LIKE command.