TSI25 TSI25 - 2 years ago 103
SQL Question

SQL Mass Character Removal

Alright, so we have a database right now that's got user login and save state info. The save state is pretty much just a JSON blob, but the client we issued has a bug in it where a '\' character gets generated, and then every subsequent time a user logs in a new '\' is added for each '\'. This is causing a pretty serious amount of wonkiness if the user logs out and back in enough times - ultimately resulting in the user not being able to log in anymore.

We fixed the client but all those save states are unusable until we fix them.

End goal here is to hit the database with an SQL query to try and drop all the '\' when there is more than one of them in a row. Recommendations? Maybe some kind of REPLACE call?

Answer Source

Use something like

UPDATE yourTable SET JsonBlob = regexp_replace(JsonBlob, '\\{2,}', '');

where '\\{2,}' means two-or-more backslashes. The first backslash is to escape the second backslash. It must be a POSIX regular expression.

PostgreSQL 9.5.3 documentation: 9.4. String Functions and Operators.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download