How to change only the last 3 characters in a Canadian Postal Code

patrick_j

Just getting started with Data Masker and would like to work on a Canadian Postal Code field. As a relatively simple way to preserve location distribution I would like to preserve the first 3 characters and randomize the last three. So the PCode A0A 0A0 would result in A0A 4F8. How would I accomplish this?

PlantBasedSQL

Hi @patrick_j great to hear you're getting started with Data Masker!
To achieve the result you've specified I would personally use the row-internal synchronization rule to target the PostalCode column. You could then use a substring to take the first 3 characters of the field and then add a random 3 characters at the end, the replacement value clause would look something like this:

SUBSTRING(PostalCode,1,3)+ ' ' + DMSPARAM1

Where DMSPARAM1 is Text, Alpha-Numeric (Formatted) with a format string of %W%W%W

Let me know if you have any more questions - always happy to help out!

patrick_j

Thanks for this direction PBS. Where will I find a definition of the Format function? Our PCodes are A9A 9A9 so I suspect that I will need a different format string.

PlantBasedSQL

My pleasure @patrick_j, happy to help!
The format function definitions should be on the rule itself when you set it, so you'll probably want %n%W%n for that format!

Happy Masking! Let me know if I can help with anything else!

PlantBasedSQL

Hi @patrick_j did the above help out at all? Were you able to get this working?
Thank you very much!

patrick_j

Yes, thank you. I was able to solve that challenge with your help.