generate random value before @ in email address

Question

Post reply

generate random value before @ in email address

dallas13

SSCertifiable

Points: 7491
More actions
April 3, 2014 at 9:37 am

#284121

Hello,
Here is my table,
^{CREATE TABLE test_43(id int, email varchar(50))}
INSERT INTO test_43 VALUES (1, 'testing1@sql.com')
INSERT INTO test_43 VALUES (2, 'testing2@sql.com')
SELECT * FROM test_43
I want to create random values for testing1 and testing2. This is how I am isolating values before @
^{SELECT REPLACE(SUBSTRING( email, 1, CHARINDEX( '@',email,1)), '@','') FROM test_43}
I also figured what random values I need by
^{SELECT LEFT(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 8)}
SELECT LEN(REPLACE(SUBSTRING( email, 1, CHARINDEX( '@',email,1)), '@','')) FROM test_43
SELECT LEFT(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), LEN(REPLACE(SUBSTRING( email, 1, CHARINDEX( '@',email,1)), '@',''))) FROM test_43
UPDATE test_43
SET email = LEFT(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), LEN(REPLACE(SUBSTRING( email, 1, CHARINDEX( '@',email,1)), '@',''))) FROM test_43
Which does work but it removes @sql.com and I dont want that to go away.

Viewing 7 posts - 1 through 6 (of 6 total)

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

dallas13 SSCertifiable Points: 7491 More actions · Answer 1

Got it.

UPDATE test_43

SET email = REPLACE( email,REPLACE(SUBSTRING( email, 1, CHARINDEX( '@',email,1)), '@',''),LEFT(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), LEN(REPLACE(SUBSTRING( email, 1, CHARINDEX( '@',email,1)), '@','')))) FROM test_43

Can not delete the question so posting answer. Its for data masking thing.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

Wouldn't this be a little easier?

UPDATE #test_43

SET email=STUFF(email, 1, CHARINDEX('@', email)-1, LEFT(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 8));

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

dallas13 SSCertifiable Points: 7491 More actions · Answer 3

Thanks Thats much easier.

The example I created has email addresses length=8 but in reality it may be any length and I wanted to just replace as many characters that are actually there before "@" .

With your example, the length will be 8 for all email addresses.

Also We can use different logic for same thing, Thanks for ur sharing ur logic.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

dallas13 (4/9/2014)
Thanks Thats much easier.
The example I created has email addresses length=8 but in reality it may be any length and I wanted to just replace as many characters that are actually there before "@" .
With your example, the length will be 8 for all email addresses.
Also We can use different logic for same thing, Thanks for ur sharing ur logic.

What range of lengths do you desire?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 5

Jeff Moden (4/9/2014)
dallas13 (4/9/2014)
Thanks Thats much easier.
The example I created has email addresses length=8 but in reality it may be any length and I wanted to just replace as many characters that are actually there before "@" .
With your example, the length will be 8 for all email addresses.
Also We can use different logic for same thing, Thanks for ur sharing ur logic.
What range of lengths do you desire?

I would have suggested this to give you lengths between 4 and 8 before Jeff replied if I wasn't on a conference call!

UPDATE test_43

SET email=STUFF(email, 1, CHARINDEX('@', email)-1, LEFT(REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 4+ABS(CHECKSUM(NEWID()))%4));

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

dallas13 SSCertifiable Points: 7491 More actions · Answer 6

Thanks both for ur help.

I think I am feeling cool and happy with my logic because it generates random characters of same length that is provided in email addresses in each row.