Noddy Encode SQL

Sometimes you want to scramble or anonymise data in a table , mainly so you can reuse or use ‘real’ data without having real names or details showing, and don’t want to screw up the formatting, so things like emails and phone numbers don’t break as well, as only doing it to certain fields

Yes I know this is a naff way to do it but given the shear number of dbas that do it this way I figured it was worth posting, also note that dont just use this as it is, move some of the replacement values around to give it your own unique values.

UPDATE **table_name** t

SET t.**field_you_want_to_update** = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

t.**field_you_want_to_update

, ‘a’, ‘g’), ‘b’, ‘w’), ‘c’, ‘c’), ‘d’, ‘x’), ‘e’, ‘f’), ‘f’, ‘v’), ‘g’, ‘b’), ‘h’, ‘y’), ‘i’, ‘m’), ‘j’, ‘u’), ‘k’, ‘e’), ‘l’, ‘p’), ‘m’, ‘l’), ‘n’, ‘a’), ‘o’, ‘t’), ‘p’, ‘z’)

, ‘q’, ‘d’), ‘r’, ‘h’), ‘s’, ‘k’), ‘t’, ‘n’), ‘u’, ‘s’), ‘v’, ‘o’), ‘w’, ‘i’), ‘x’, ‘q’), ‘y’, ‘r’), ‘z’, ‘j’), ‘A’, ‘G’), ‘B’, ‘I’), ‘C’, ‘H’), ‘D’, ‘M’), ‘E’, ‘A’), ‘F’, ‘Z’)

, ‘G’, ‘Y’), ‘H’, ‘L’), ‘I’, ‘B’), ‘J’, ‘K’), ‘K’, ‘J’), ‘L’, ‘N’), ‘M’, ‘C’), ‘N’, ‘T’), ‘O’, ‘R’), ‘P’, ‘S’), ‘Q’, ‘X’), ‘R’, ‘D’), ‘S’, ‘W’), ‘T’, ‘E’), ‘U’, ‘F’), ‘V’, ‘F’)

, ‘W’, ‘P’), ‘X’, ‘U’), ‘Y’, ‘V’), ‘Z’, ‘Q’), ‘0’, ‘9’), ‘1’, ‘3’), ‘2’, ‘6’), ‘3’, ‘7’), ‘4’, ‘1’), ‘5’, ‘2’), ‘6’, ‘3’), ‘7’, ‘0’), ‘8’, ‘4’), ‘9’, ‘5’)

 

Leave a Reply

Your email address will not be published.