Updating a field twice in one statement

Question

Post reply

Updating a field twice in one statement

s_osborne2

SSCertifiable

Points: 6726
More actions
May 8, 2012 at 3:37 am

#255608

Hi,
I'm having to write an anonymisation script for our production DB's so we can bring them into the Dev and QA environments and i'm pretty sure the best way of going about this in our case is to use RegEx CLR functions to update the strings.
I've used the attached script to setup the CLR functions and i'm able to use them without any problems but i have the following question....
Is it possible to update the same field twice with one statement as per the below:
CREATE TABLE #test
(
string VARCHAR(50)
)
CREATE TABLE #test2
(
maskedString VARCHAR(50)
)
INSERT INTO #test VALUES
('111 Test-Test'),
('222 Test Test'),
('333 Test Test Test')
-- Update 1
UPDATE #test
SET string = dbo.RegexReplace(string, '[a-zA-Z]', 'X'),
string = dbo.RegexReplace(string, '[0-9]', '9')
FROM #test
-- Update 2
--UPDATE #test
--SET string = dbo.RegexReplace(string, '[0-9]', '9')
--FROM #test
SELECT * FROM #test
DROP TABLE #test
DROP TABLE #test2
The example above would be similar to updating an address line. Imagine you have to set all nos. to 9 and all letters to X. I'd like to do this in one statement, thereby scanning the table only once to update all the rows. The only solution i can come up with at the moment means i have to run the update twice, once to replace the letters, and once to replace the nos.
If anyone knows of a way i can do this by using one statement rather than two i'd be grateful if you could share your solution. 🙂
Thanks,
Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

Viewing 3 posts - 1 through 2 (of 2 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

UPDATE #test

SET string = dbo.RegexReplace(dbo.RegexReplace(string, '[0-9]', '9'), '[a-zA-Z]', 'X')

FROM #test

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 2

Cracking work!! (seems so simple when you see it). Thanks very much.

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne