Create /replace the sensitive data ( first Name , last Name ) with some random values generated

  • Hello ,
     I have a situation where the company does not want to show actual values to the user ( loan information) as we are just sending a test file to the client , we do not want to send our actual /sensitive information. 
    This information includes laonnumber , Firstname , last name , address etc.

    Example : For loannnumber i genertaed random sequential numbers .

    But with Names I am little confused. Is there a way to generate a random firstnames , lastnames , address ?

  • komal145 - Wednesday, April 18, 2018 9:24 AM

    Hello ,
     I have a situation where the company does not want to show actual values to the user ( loan information) as we are just sending a test file to the client , we do not want to send our actual /sensitive information. 
    This information includes laonnumber , Firstname , last name , address etc.

    Example : For loannnumber i genertaed random sequential numbers .

    But with Names I am little confused. Is there a way to generate a random firstnames , lastnames , address ?

    You could scramble the names from AdventureWorks or WideWorldImporters (depending on your version) to assign them randomly to your data. There's also a Red-gate tool for that, but you need to pay for it.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Wednesday, April 18, 2018 9:30 AM

    komal145 - Wednesday, April 18, 2018 9:24 AM

    Hello ,
     I have a situation where the company does not want to show actual values to the user ( loan information) as we are just sending a test file to the client , we do not want to send our actual /sensitive information. 
    This information includes laonnumber , Firstname , last name , address etc.

    Example : For loannnumber i genertaed random sequential numbers .

    But with Names I am little confused. Is there a way to generate a random firstnames , lastnames , address ?

    You could scramble the names from AdventureWorks or WideWorldImporters (depending on your version) to assign them randomly to your data. There's also a Red-gate tool for that, but you need to pay for it.

    Thanks for suggestion but 1) I need to download the adventure works on Development server  for that right? No access to do that ......  I have version 2016. Is there any way through?
    2) Company wont pay anything for now ( redgate ) tool for this purpose.

  • Well technically you can also just use sequential first/last names 😛  

    Firstname1, Lastname1
    Firstname2, Lastname2
    Firstname3, Lastname3
    .
    .
    .

  • ZZartin - Wednesday, April 18, 2018 9:52 AM

    Well technically you can also just use sequential first/last names 😛  

    Firstname1, Lastname1
    Firstname2, Lastname2
    Firstname3, Lastname3
    .
    .
    .

    Lol...this looks not real data ......need to generate data which looks like real.

  • You can get a list of common names from sources like the Census Bureau.  They have separate files available for first names by gender, and last names:
    https://www.census.gov/topics/population/genealogy/data/1990_census/1990_census_namefiles.html
    I import them into tables and use these to randomly generate names when I need them.

  • komal145 - Wednesday, April 18, 2018 10:09 AM

    ZZartin - Wednesday, April 18, 2018 9:52 AM

    Well technically you can also just use sequential first/last names 😛  

    Firstname1, Lastname1
    Firstname2, Lastname2
    Firstname3, Lastname3
    .
    .
    .

    Lol...this looks not real data ......need to generate data which looks like real.

    Why? Are you not telling the third party they're getting fake data?

  • komal145 - Wednesday, April 18, 2018 9:42 AM

    Luis Cazares - Wednesday, April 18, 2018 9:30 AM

    komal145 - Wednesday, April 18, 2018 9:24 AM

    Hello ,
     I have a situation where the company does not want to show actual values to the user ( loan information) as we are just sending a test file to the client , we do not want to send our actual /sensitive information. 
    This information includes laonnumber , Firstname , last name , address etc.

    Example : For loannnumber i genertaed random sequential numbers .

    But with Names I am little confused. Is there a way to generate a random firstnames , lastnames , address ?

    You could scramble the names from AdventureWorks or WideWorldImporters (depending on your version) to assign them randomly to your data. There's also a Red-gate tool for that, but you need to pay for it.

    Thanks for suggestion but 1) I need to download the adventure works on Development server  for that right? No access to do that ......  I have version 2016. Is there any way through?
    2) Company wont pay anything for now ( redgate ) tool for this purpose.

    You can also use your database and just scramble names from your table.
    This is a sample code but you can adapt it as you need it. It uses a copy from the AdventureWorks table Person.

    WITH cteNames AS(
      SELECT p.FirstName, p.LastName
       ROW_NUMBER() OVER(ORDER BY NEWID()) AS ORDER_1,
       ROW_NUMBER() OVER(ORDER BY NEWID()) AS ORDER_2,
       ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS ORDER_Original
      FROM PersonCopy AS p
    )
    UPDATE p SET
      FirstName = fn.FirstName,
      LastName = ln.LastName
    FROM cteNames p
    JOIN cteNames fn ON p.ORDER_Original = fn.ORDER_1
    JOIN cteNames ln ON p.ORDER_Original = ln.ORDER_2;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Wednesday, April 18, 2018 10:32 AM

    komal145 - Wednesday, April 18, 2018 9:42 AM

    Luis Cazares - Wednesday, April 18, 2018 9:30 AM

    komal145 - Wednesday, April 18, 2018 9:24 AM

    Hello ,
     I have a situation where the company does not want to show actual values to the user ( loan information) as we are just sending a test file to the client , we do not want to send our actual /sensitive information. 
    This information includes laonnumber , Firstname , last name , address etc.

    Example : For loannnumber i genertaed random sequential numbers .

    But with Names I am little confused. Is there a way to generate a random firstnames , lastnames , address ?

    You could scramble the names from AdventureWorks or WideWorldImporters (depending on your version) to assign them randomly to your data. There's also a Red-gate tool for that, but you need to pay for it.

    Thanks for suggestion but 1) I need to download the adventure works on Development server  for that right? No access to do that ......  I have version 2016. Is there any way through?
    2) Company wont pay anything for now ( redgate ) tool for this purpose.

    You can also use your database and just scramble names from your table.
    This is a sample code but you can adapt it as you need it. It uses a copy from the AdventureWorks table Person.

    WITH cteNames AS(
      SELECT p.FirstName, p.LastName
       ROW_NUMBER() OVER(ORDER BY NEWID()) AS ORDER_1,
       ROW_NUMBER() OVER(ORDER BY NEWID()) AS ORDER_2,
       ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS ORDER_Original
      FROM PersonCopy AS p
    )
    UPDATE p SET
      FirstName = fn.FirstName,
      LastName = ln.LastName
    FROM cteNames p
    JOIN cteNames fn ON p.ORDER_Original = fn.ORDER_1
    JOIN cteNames ln ON p.ORDER_Original = ln.ORDER_2;

    Thank you. It works for me 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply