What are the challenges you face when working across database platforms? Take the survey

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

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?


  • Options
    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!
  • Options
    patrick_jpatrick_j Posts: 13 Bronze 2
    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.
  • Options
    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!
  • Options
    Hi @patrick_j did the above help out at all? Were you able to get this working?
    Thank you very much!
  • Options
    patrick_jpatrick_j Posts: 13 Bronze 2
    Yes, thank you. I was able to solve that challenge with your help.
Sign In or Register to comment.