substitution rule with random values in a range

kevinumass

I have a column which has the integers 0 through 16 as valid values.  I want Data Masker to generate new random values in that same range, but NOT the same as the existing value.  How do I get DM to generate new random values that have to be different from the current existing value for any given row?  thx

PlantBasedSQL

Hi @kevinumass thank you for your post!

I would say the best way to achieve what you're looking for would be something like a Row-Internal Sync rule with a CASE statement. Target the column you want to mask (in this example I'll use real_num), set both DMSPARAM1 and DMSPARAM2 to be Numbers, Integer (Random) with high and low bounds as 16 and 0 respectively.
Then in the Column Replacement Values Clause go for something like

"CASE WHEN DMSPARAM1 = real_num THEN DMSPARAM2 ELSE DMSPARAM1 END)"

You could even go for an additional layer of logic by adding a third DMSPARAM or doing additional WHEN/THEN to say if DMSPARAM2 also = real_num and is greater than 0, then just minus 1 from the number, else add 1. The kicker here is that after the operation nobody will know which row got a DMSPARAM and which just had 1 minused/added to it.

There's more info on Row-Internal rules here: https://www.red-gate.com/hub/university/sql-provision/sql-provision under "Synchronizing Masked Information"

I hope this helps? Let me know if you have any additional questions!

kevinumass

thanks.  I tried this with surnames also.  Always leaves me with a handful of non-masked surnames.  Not sure if I have too many records to update or the source data set is too small.  Either way, the case statement doesn't appear to be working.  Need to move on and find another way.

PlantBasedSQL

Hi @kevinumass did you get this requirement working at all after the above thread?
I've been thinking about this a bit more recently and have a couple of other ideas that might be able to help out, but will leave off if it's not longer required! Thank you very much.