June 4, 2013 at 5:50 pm
Hello Everyone
I hope that you all are having a very nice day.
I am wanting to change all the FirstName and LastName values in a table. This is more for Demo security really. Creating some fake names from existing names. These are stored in two separate columns. I cannot think of any way to easily do this. I need approx 25,000 rows. LOL There are names in the columns currently, I would just like to change them so that the entire record does not show an actual person. I want to use real names, not anything like FirstName1, FirstName2, etc....
So Frank Smith, would be changed to Joe Jackson
I have already changed all the dates and numeric values, those were simple.
I greatly appreciate any and all assistance, suggestions and comments.
Thanks
Andrew SQLDBA
June 4, 2013 at 6:02 pm
How about using names from AdventureWorks DB?
June 4, 2013 at 6:14 pm
Maybe some code like this
SELECT FirstName, LastName
FROM(
SELECT FirstName,
ROW_NUMBER() OVER(ORDER BY FirstName) rn
FROM( SELECT DISTINCT FirstName FROM MyTable)x) a
JOIN (
SELECT LastName,
ROW_NUMBER() OVER(ORDER BY LastName DESC) rn
FROM( SELECT DISTINCT LastName FROM MyTable)y ) b ON a.rn = b.rn
June 4, 2013 at 8:47 pm
Here's another option
with sampleData as (
SELECT FirstName, LastName, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES
('Robin','Bacon'),
('Owen','Twomey'),
('James','Kendall'),
('John','Cavanagh'),
('Lola','Oxborough'),
('Thomas','Archibald'),
('Mathias','Kearns'),
('Christine','Winger'),
('Barry','Kane'),
('Rereahu','Gray'),
('Douglas','Christian'),
('Jennifer','Clark'),
('Kenneth','Court'),
('Paul','Pedersen'),
('Bruce','McKelvie'),
('David','McCrary'),
('Paul','Allison'),
('Linda','Cawthorn'),
('Noelene','Schellhorn'),
('Patricia','Pedersen'),
('Ivan','Hill'),
('Stewart','Leys'),
('James','Austin'),
('Roger','McManaway'),
('Katherine','Edmonds'),
('Bryan','Wilson'),
('James','Jones'),
('William','Healey'),
('Caroline','Rae'),
('Stephen','Mullins'),
('Graeme','Gray'),
('Gary','Gibbens'),
('Annie','Oxborough'),
('Emma','Kennedy'),
('Patricia','Wii'),
('Raymond','Winger'),
('Judith','Owen'),
('Ruby','McCrary'),
('Peter','Waterhouse'),
('Gaye','Cox'),
('Barbara','Hill'),
('Jeanette','Patchett'),
('Norman','Forbes'),
('Andrew','Forsyth'),
('Aldyth','Edmonds'),
('Mick','Stohr'),
('Nicola','Howie'),
('Linda','Gray'),
('Fenella','Silva'),
('Allan','McCandless'),
('Gillian','Scott'),
('Garth','Woodcock'),
('Peter','Woodcock'),
('Fenella','Waterhouse'),
('Patricia','Leys'),
('Paul','Sykes'),
('Richard','Fortzer'),
('Dean','McKelvie'),
('Cuan','Fortzer'),
('Vaughan','Heymons'),
('Donald','Toseland'),
('Alan','Sclater'),
('Nancy','Ollerenshaw'),
('Eddie','Buhler'),
('Lewis','Taylor'),
('Kerin','Medland'),
('Carlton','Forsyth-King'),
('Opie','Cleland'),
('Gail','Oshannessy'),
('Stuart','Gray')
) AS N (FirstName, LastName)
)
,RandFirstName AS (SELECT FirstName, ROW_NUMBER() OVER (ORDER BY RAND(CAST(NEWID() AS VARBINARY))) N FROM sampledata a)
,RandLastName AS (SELECT LastName, ROW_NUMBER() OVER (ORDER BY RAND(CAST(NEWID() AS VARBINARY))) N FROM sampledata a)
SELECT f.FirstName, l.LastName, a.FirstName, a.LastName
FROM sampledata a
INNER JOIN RandFirstName f ON a.N = f.N
INNER JOIN RandLastName l ON a.N = l.N;
Sample data was pre randomized, but it still came up with a name of someone I know:-) Fortunately he wasn't in the original list.
Edit: Just tested this over around 500,000 rows and had it return 1 or 2 non randomized names 6 out 10 times. They were generally common names though, eg John Smith
June 5, 2013 at 8:32 am
Or use any of the free fake name generators on the interwebz. Here is one I have used in the past, there are plenty of others.
http://homepage.net/name_generator/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply