Solved

Update Table With Random Strings

Posted on 2008-10-17
3
770 Views
Last Modified: 2012-06-27
Dear Experts,

I have a table with 1,000,000 rows.  I have now created a new column in this table called random_strings where I somehow need to update the whole table so each of these 1,000,000 records has a random string in the new column.  How would I go about doing this?

The strings must be upper case and 5 characters in length.  However, the number of these 5 character strings needs to be random to per row separated by a space character, and there must be no more than 10 of these 5 character strings per row and no less than 1 of these 5 character strings per row.  For example

row1 = asdfg xcvbn
row2 = lkjdk eidkf dkeld ghdke slwyd
row3 = utire
...
row999998 =qqqqq asasa gfgre hggff nnnmm qqqqq asasa gfgre hggff nnnmm
row999999 = nnnnn aaaaa fffff
row1000000 = jfkdh eirut qywir fkdld ghfnd ppooi xcsas

Can anyone help me create a stored procedure to do this?
0
Comment
Question by:narmi2
3 Comments
 
LVL 5

Accepted Solution

by:
jfmador earned 500 total points
ID: 22746993
Hello to build your 5char value you can use

DECLARE @text as varchar(5)
SET @text = char(65 + cast(rand() * 26 as integer)) + char(65 + cast(rand() * 26 as integer)) + char(65 + cast(rand() * 26 as integer)) + char(65 + cast(rand() * 26 as integer)) + char(65 + cast(rand() * 26 as integer))

To add a maximum of 10 string you store a random number in a Integer and use a while decreasing this value of one at each loop until it is equal to 0

DECLARE @count as integer
DECLARE @text as varchar(60)
SET @count = 1 + cast(rand() * 9 as integer)
while @count > 0
begin
SET @text = @Text + char(65 + cast(rand() * 26 as integer)) + char(65 + cast(rand() * 26 as integer)) + char(65 + cast(rand() * 26 as integer)) + char(65 + cast(rand() * 26 as integer)) + char(65 + cast(rand() * 26 as integer)) + ' '
SET @Count = @Count - 1
end


You can put this code in a function and use an update statement to update all your row
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now