Scrambling the Contents - SQL Server Forums

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

Transact-SQL (2000)

Scrambling the Contents

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2008-10-29 : 15:46:22

Anyone have a nifty little scramble function? I'm turning over some data and want to scramble the actual names and addresses and phone numbers in order to create dummy data. I figure if I can use it in a update query life will be just grand!

Thanks!!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-29 : 17:07:31

One possible way.... using cursor


declare @str varchar(8000), @numb int, @newstr varchar(8000)
set @str = 'ABCDEFG 123'
set @newstr = ''
declare cur cursor for
select number from master..spt_values where type = 'P' and number between 1 and len(@str)
order by newid()

open cur

fetch next from cur into @numb

while(@@FETCH_STATUS = 0)
BEGIN
  select @newstr = substring(@str,@numb,1)+@newstr
  FETCH NEXT FROM cur into @numb
END

print @newstr

close cur
deallocate cur

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2008-10-29 : 17:24:22

But Tara, the Almighty SQL Goddess, (tkizer) told me never to play with cursors! Can we do this as a function?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-29 : 17:29:59

never play with fire unless you are a chef right?

In this case, cursor is not used to perform update but to concatenate strings. There is no data access during the looping :D

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-29 : 17:51:39

I have another solution in SQL 2005, which is much better. You wanna see?

There must be an easier way of doing this but, you'll need a view of random to make this work. The limitation is that your varchar can not be greater than 255.


create view VW_RandomNumber
as 
select number, newid() as randid from master..spt_values where type = 'P'


crate function dbo.fn_scrumble(@str varchar(8000))
returns varchar(8000)
as
BEGIN
declare @numb int, @newstr varchar(8000)
--set @str = '123 MAIN STREET'
set @newstr = ''
declare cur cursor for
select number from vw_RandomNumber where number between 1 and len(@str)
order by randid

open cur

fetch next from cur into @numb

while(@@FETCH_STATUS = 0)
BEGIN
  select @newstr = substring(@str,@numb,1)+@newstr
  FETCH NEXT FROM cur into @numb
END

close cur
deallocate cur

return @newstr
END

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-29 : 19:07:41

This code will take 100000 randomly selected first names, last names, and addresses from a table, and combine them to produce 100000 randomly mixed combinations.

Modify to suit your needs, but this should scale well to produce large sets of data.


-- Get random selections of First Name, Last Name, and Address
select top 100000 FIRST_NAME into #t1 from MyTable order by newid()
select top 100000 LAST_NAME into #t2  from MyTable order by newid()
select top 100000 STREET_ADDRESS1 into #t3 from MyTable order by newid()

-- Add Seq column as lookup key
select seq = identity(int,1,1), FIRST_NAME into #t1a from #t1 order by FIRST_NAME
select seq = identity(int,1,1), LAST_NAME into #t2a from #t2 order by LAST_NAME
select seq = identity(int,1,1), STREET_ADDRESS1 into #t3a from #t3 order by STREET_ADDRESS1

-- Get 100000 random Seq in range of 1 to 100000 for each column
select
	Seq1 = (abs(convert(int,convert(varbinary(16),newid())))%100000)+1 ,
	Seq2 = (abs(convert(int,convert(varbinary(16),newid())))%100000)+1 ,
	Seq3 = (abs(convert(int,convert(varbinary(16),newid())))%100000)+1
into
	#t4
from
	#t1

-- Join to random Seq
select
	a.FIRST_NAME,
	b.LAST_NAME,
	c.STREET_ADDRESS1
into
	#t5
from
	#t4 x
	join
	#t1a a on x.Seq1 = a.Seq
	join
	#t2a b on x.Seq2 = b.Seq
	join
	#t3a c on x.Seq3 = c.Seq

-- Randomly select 100 rows
select top 100 * from #t5 order by newid()

drop table #t1
drop table #t2
drop table #t3
drop table #t4
drop table #t5
drop table #t1a
drop table #t2a
drop table #t3a

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-30 : 05:12:32

quote:

Originally posted by hanbingl

One possible way.... using cursor


declare @str varchar(8000), @numb int, @newstr varchar(8000)
set @str = 'ABCDEFG 123'
set @newstr = ''
declare cur cursor for
select number from master..spt_values where type = 'P' and number between 1 and len(@str)
order by newid()

open cur

fetch next from cur into @numb

while(@@FETCH_STATUS = 0)
BEGIN
  select @newstr = substring(@str,@numb,1)+@newstr
  FETCH NEXT FROM cur into @numb
END

print @newstr

close cur
deallocate cur

I would do


declare @str varchar(8000), @numb int
declare @t table(data varchar(1))
set @str = 'ABCDEFG 123'
insert into @t
select substring(@str,number,1) from master..spt_values where type = 'P' and number between 1 and len(@str)
order by newid()

declare @result varchar(8000)
select @result=coalesce(@result,'')+data from @t

select @str as original,@result as scrambled

Madhivanan

Failing to plan is Planning to fail

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2008-10-30 : 09:32:20

Madhivanan, hanbingl and Col. Jones.....thanks so much for your innovative solutions!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources