quote:

---

Originally posted by Seventhnight

you gotta be more specific than that

modify how... based on what... how many records... why 'iterate'?

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

---

quote:

---

Originally posted by Seventhnight

randomly generating as in:
32187623423
439834i3644
or
askdjgasfas
orblkjvergm
or
298ckjbvds9tr
kjer9ovdi45hv


how about you give me a sample to work with... you give me some records to start from, and let me know what you want them to look like... and I'll try to fix you up

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

---

quote:

---

Originally posted by madhivanan

>>I need to loop thru all the rows in the table and change the values of two columns in each row with values I'll be randomly generating. The record count could potentially get into the high hundreds of thousands.

Is this?

Update yourTable set col1=function(col1),col2=function(col2)

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by madhivanan

Do you need something like this?

Update yourTable set col1=cast(newid() as varchar(36)),col2=cast(newid() as varchar(36))


Madhivanan

Failing to plan is Planning to fail

---

Create View dbo.RandomDigit
As
	Select top 1 n
	From 
		(
		Select n=0 Union All
		Select 1 Union All
		Select 2 Union All
		Select 3 Union All
		Select 4 Union All
		Select 5 Union All
		Select 6 Union All
		Select 7 Union All
		Select 8 Union All
		Select 9
		) A
	Order By newId()
Go

Create View dbo.RandomCharacter
As
	Select top 1 n
	From 
		(
		Select n='a' Union All
		Select 'b' Union All
		Select 'c' Union All
		Select 'd' Union All
		Select 'e' Union All
		Select 'f' Union All
		Select 'g' Union All
		Select 'h' Union All
		Select 'i' Union All
		Select 'j' Union All
		Select 'k' Union All
		Select 'l' Union All
		Select 'm' Union All
		Select 'n' Union All
		Select 'o' Union All
		Select 'p' Union All
		Select 'q' Union All
		Select 'r' Union All
		Select 's' Union All
		Select 't' Union All
		Select 'u' Union All
		Select 'v' Union All
		Select 'w' Union All
		Select 'x' Union All
		Select 'y' Union All
		Select 'z'
		) A
	Order By newId()
Go
Create Function getRandomDigit()
Returns int As
Begin
	Declare @rValue int

	Select @rValue = n from dbo.RandomDigit

	Return @rValue
End
Go
Create Function getRandomChar()
Returns varchar(1) As
Begin
	Declare @rValue varchar(1)

	Select @rValue = n from dbo.RandomCharacter

	Return @rValue
End
Go
Create Table #TempTable
(
id int identity(1,1),
address varchar(100),
address2 varchar(100)
)

Insert Into #TempTable
Select '3546 Holland Drive Apt: 239', null Union All
Select '439 Bill Gates Rd', null Union All
Select 'Intersection of 5th & Main', null

Declare @maxLen int,
	@i int

Set @i = 1
Set @MaxLen = (Select max(len(address)) From #TempTable)

Select * From #TempTable

While @i < @maxLen
Begin
	Update #TempTable
	Set address2 = isnull(address2,'') + 
		case 
			when substring(address,@i,1) like '[0-9]' then convert(varchar,dbo.getRandomDigit())
			when substring(address,@i,1) collate SQL_Latin1_General_CP1_CS_AS like '[abcdefghijklmnopqrstuvwxyz]' collate SQL_Latin1_General_CP1_CS_AS then dbo.getRandomChar()
			when substring(address,@i,1) collate SQL_Latin1_General_CP1_CS_AS like '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]' collate SQL_Latin1_General_CP1_CS_AS then upper(dbo.getRandomChar()) 
			else substring(address,@i,1) end
	From #TempTable
		
	Set @i = @i + 1
End

Select * From #TempTable


Drop Table #TempTable
	
Go
Drop View dbo.RandomDigit
Drop View dbo.RandomCharacter
Drop Function dbo.getRandomDigit
Drop Function dbo.getRandomChar

quote:

---

Originally posted by Seventhnight

Bump... I thought this was a fun topic... and then the guy just disappears

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

---

quote:

---

Originally posted by madhivanan

>>how do I become a student of yours...

Visit this
http://sqlteam.com/forums/pop_profile.asp?mode=display&id=11086

Then click
Find all non-archived posts by Seventhnight

See his responses to all questions. At the end you become Master

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by Seventhnight

I think this will do it... though it may not be REAL FAST...

Create View dbo.RandomDigit
As
	Select top 1 n
	From 
		(
		Select n=0 Union All
		Select 1 Union All
		Select 2 Union All
		Select 3 Union All
		Select 4 Union All
		Select 5 Union All
		Select 6 Union All
		Select 7 Union All
		Select 8 Union All
		Select 9
		) A
	Order By newId()
Go

Create View dbo.RandomCharacter
As
	Select top 1 n
	From 
		(
		Select n='a' Union All
		Select 'b' Union All
		Select 'c' Union All
		Select 'd' Union All
		Select 'e' Union All
		Select 'f' Union All
		Select 'g' Union All
		Select 'h' Union All
		Select 'i' Union All
		Select 'j' Union All
		Select 'k' Union All
		Select 'l' Union All
		Select 'm' Union All
		Select 'n' Union All
		Select 'o' Union All
		Select 'p' Union All
		Select 'q' Union All
		Select 'r' Union All
		Select 's' Union All
		Select 't' Union All
		Select 'u' Union All
		Select 'v' Union All
		Select 'w' Union All
		Select 'x' Union All
		Select 'y' Union All
		Select 'z'
		) A
	Order By newId()
Go
Create Function getRandomDigit()
Returns int As
Begin
	Declare @rValue int

	Select @rValue = n from dbo.RandomDigit

	Return @rValue
End
Go
Create Function getRandomChar()
Returns varchar(1) As
Begin
	Declare @rValue varchar(1)

	Select @rValue = n from dbo.RandomCharacter

	Return @rValue
End
Go
Create Table #TempTable
(
id int identity(1,1),
address varchar(100),
address2 varchar(100)
)

Insert Into #TempTable
Select '3546 Holland Drive Apt: 239', null Union All
Select '439 Bill Gates Rd', null Union All
Select 'Intersection of 5th & Main', null

Declare @maxLen int,
	@i int

Set @i = 1
Set @MaxLen = (Select max(len(address)) From #TempTable)

Select * From #TempTable

While @i < @maxLen
Begin
	Update #TempTable
	Set address2 = isnull(address2,'') + 
		case 
			when substring(address,@i,1) like '[0-9]' then convert(varchar,dbo.getRandomDigit())
			when substring(address,@i,1) collate SQL_Latin1_General_CP1_CS_AS like '[abcdefghijklmnopqrstuvwxyz]' collate SQL_Latin1_General_CP1_CS_AS then dbo.getRandomChar()
			when substring(address,@i,1) collate SQL_Latin1_General_CP1_CS_AS like '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]' collate SQL_Latin1_General_CP1_CS_AS then upper(dbo.getRandomChar()) 
			else substring(address,@i,1) end
	From #TempTable
		
	Set @i = @i + 1
End

Select * From #TempTable


Drop Table #TempTable
	
Go
Drop View dbo.RandomDigit
Drop View dbo.RandomCharacter
Drop Function dbo.getRandomDigit
Drop Function dbo.getRandomChar

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

---

quote:

---

Originally posted by tchinedu

PS: the main constraint is that I have to do this in a stored procedure...

---