Ian Winter

You may or may not know about the wonderful vertical tab character 0x0B. It’s a character that’s not easy to see and can cause issues across a number of different programming languages. In my case a Rails developer was having trouble passing some data from an API. A JSON parser was throwing errors about a specific character. Originally we suspected line breaks were causing the issue but after more digging and examination using Textmate and the Text bundle we spotted the invisible characters and having converted it to hex found out it was 0x0B. A quick Google brings back quite a few results of people having the same problem.

In order to find them in the database I knocked up the following SQL:

SELECT * FROM table WHERE data LIKE CONCAT('%',CHAR(0x0B),'%');

Then to replace them quickly (assuming the 3 records have ID’s 1,2 and 3):

UPDATE table SET data = REPLACE(data,CHAR(0x0B),'') WHERE id IN (1,2,3);