SQL Server Performance Forum – Threads Archive

replcing dat a with some.junc value

Hi Friends. I have to write a query or stored procedure for following requirement. In a table let’s say one column name is there. I need to replace each record of name field with some junc data. like My initial table is
table 1
name raaj
Suraj
ravi My result should be something like brsa
atfge
frt I got some clue like..I will take the ascii value of each char and add some vale to that ascii value again convert that to char. But how can i improve my performance.How to go for other dat types other than text and interger. Please help me.Let me know if the query is not clear Surjit

You can convert it to varbinary
Declare @mask table (data varchar(10))
insert into @mask
Select ‘raaj’ union all
Select ‘Suraj’ union all
Select ‘ravi’
Select cast(data as varbinary(100)) as JunkData from @mask
Madhivanan Failing to plan is Planning to fail

quote:Originally posted by Madhivanan You can convert it to varbinary
Declare @mask table (data varchar(10))
insert into @mask
Select ‘raaj’ union all
Select ‘Suraj’ union all
Select ‘ravi’
Select cast(data as varbinary(100)) as JunkData from @mask
Madhivanan Failing to plan is Planning to fail

Thanks Madhivanan, I need to update the records with some valid name…like ‘raaj’ should be replaced with ‘tcci’.How can i update the records..if i go record by record.The performance will be degrade.Pleas help me. Surjit

Why do you want to update it?
Just use Select statement to mask it
What is the purpose of doing this? Madhivanan Failing to plan is Planning to fail

quote:Originally posted by Madhivanan Why do you want to update it?
Just use Select statement to mask it
What is the purpose of doing this? Madhivanan Failing to plan is Planning to fail

The client can not give us live data,hence he wants to replce the live data with soem junc we need to develop a tool for them. Thanks Surjit

The fun part is of course that if you provide the tool, you stand a good chance of reversing the process as well, unmasking the data. The serious part is that your company can sign a confidentiality agreement, which has legal standing, as far as I’m aware. Of course you could also generate fake data, somewhere along these lines: (1) Analyze the data structure, identifying all tables that have FK relationships. (2) Generate alphanumeric codes as ‘aaa1’, ‘aaa2’, ‘aaa3’, etc. for alphanumeric FK values in the source tables, using a different prefix for different source tables. Generate numeric values in similar ways. (3) Generate the main data. I remember Excel has a tool to generate lists of increasing values: Edit > Fill > Series, so you mght take advantage of that as well.

Hm, if you simply need a dummy data generator, just look st this:http://research.microsoft.com/%7EGray/DBGen/
It’s the same tool, one can also find on the TPC site. It’s free with all the C++ source included. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

quote:Originally posted by FrankKalis Hm, if you simply need a dummy data generator, just look st this:http://research.microsoft.com/%7EGray/DBGen/
It’s the same tool, one can also find on the TPC site. It’s free with all the C++ source included. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Thanks a lot Adriaan,FrankKalis,Madhivanan for your useful thoughts. Your views were realy helpful to me to proceed further. surjit

I am interested to know what your further proceed would be [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan I am interested to know what your further proceed would be [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> Hi Madhibanan, In brief, I used record set, checking the data type of the field .Based on the datatype,I am calling different functions to change the data to some junc value.like..if the data type is date..i am adding some random value to it.If the data type is numeric or alphanumeric..i am converting that to ascii and addingsome random value to that and again changing to char..I hope it answers. but i am facing problem when there are relation(FK).We can not change the value to child table before parent table.[Adriaan] can you please explain in detail as you mentioned in your previous mail for relations.Is it possible something like..if we change somerecords in parent table..it will reflect in child table. like Parenttable child table PK v1 FK,v2,v3 2 t 3 tt uu 3 h 2 ii pp 4 y 2 yy re 4 tt hh if I chnage 2 in parent table with 8, then all corresponding 2 s in child table will change to 8 result PK v1 FK,v2,v3 8 t 3 tt uu 3 h 8 ii pp 4 y 8 yy re 4 tt hh Thank you all for yor help.Waiting for your reply Surjit

]]>