anonymous a column in the table...? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

anonymous a column in the table...?

Author

Topic

sokun2003
Starting Member

3 Posts

Posted - 2004-01-12 : 05:40:02

Hi,
how can i transforme one column ( pename char (50)) of my table to be
anonymous ? there're approx. 20000 rows in this table.

tks in advance

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2004-01-12 : 05:53:50

Could you explain, what you meant by making a column anonymous?

--
HTH,
Vyas
http://vyaskn.tripod.com

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-12 : 10:01:55

[code]
USE Northwind

CREATE TABLE Test (ID int IDENTITY(1,1), Test_Numeric decimal(2,0))

INSERT INTO TEST (Test_Numeric) SELECT 1.12

SELECT * FROM Test

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
EXECUTE sp_rename N'dbo.Test.Test_Numeric', N'Tmp_anonymous', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.Test.Tmp_anonymous', N'anonymous', 'COLUMN'
GO
COMMIT

DROP TABLE Test
GO
[/code]

Sorry...couldn't help myself...

Brett

8-)

sokun2003
Starting Member

3 Posts

Posted - 2004-01-13 : 09:24:40

hi,

i have a following table :

PeName adresse account_n et ...
==============================================
Dupont
TOTO1
TOTO2
etc...
(=15000 rows)

and i have been asked to transform the column Pename be anonymous,
it means to modify the whole colum pename to different "unreal" names.

tks in advance...

quote:
Originally posted by VyasKN

Could you explain, what you meant by making a column anonymous?

--
HTH,
Vyas
http://vyaskn.tripod.com

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-13 : 10:03:20

You mean change the data?
something like

declare @i int
select @i = 0
set rowcount 1
while exists (select * from tbl where Pename not like 'name%')
begin
select @i = @i + 1
update tbl set Pename = 'name' + right('00000' + convert(varchar(5),@i),5)
end
set rowcount 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-01-13 : 10:06:10

There is no SQL function that I know of to do this. Would it be sufficient to have the company names like 'Company A', 'Company B' etc? That could be done very easily.

Alternatively, if you already have a unique identifier for each row you could set PeName to be 'Company ' + account_n (for example). This would still allow you to identify the company but hide it's name.

Raymond

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-13 : 10:07:48

or
declare @s varchar(10)
select @s = 'name1'
update tbl set @s = Pename = left(@s,4) + convert(varchar(10),convert(int,right(@s,len(@s)-4))+1)

Does a similar thing to my post above but is simpler.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-13 : 10:22:56

You need to do this for data security reasons...right?

And you need to deliver "production" level data to developers...

We just built a whole series a "dynamic" scrambling routines to do this...is that what you're trying to do?

Brett

8-)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-01-13 : 12:47:06

Could you post the routines, or a "scrambled" version of them? :) That would be kewl.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-13 : 12:55:42

quote:
Originally posted by derrickleggett

Could you post the routines, or a "scrambled" version of them? :) That would be kewl.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

um...not really....sorry...suffice it to say that the data can not be tied back to the originator, and there is no translation key to get back...

Brett

8-)

sokun2003
Starting Member

3 Posts

Posted - 2004-01-14 : 06:23:55

quote:
Originally posted by X002548

You need to do this for data security reasons...right?

And you need to deliver "production" level data to developers...

We just built a whole series a "dynamic" scrambling routines to do this...is that what you're trying to do?

Brett

8-)

Yeah...u r right...can i have your precious advices ? :=)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources