SQL query that will update a field for every row in a table with a different random 4 letter uppercase string

Hi Richard,

How many rows do you need to update?

And can you clarify "I and O, EG BHGR"? Looked at one way it suggests that a string of 1 to 4 characters is desired.  Look as another way and I, O, EG, and GHGR can't be part of the string.

How frequently does this query need to run?

Is there an event that triggers it?

This is a great solution!
Unfortunately, it does not ensure the uniqueness, so two rows can obtain the same value. My test shows ca 3% of duplicities.
The following query shows duplicate values (which differ from call to call)

SELECT unq, COUNT(*) FROM (
SELECT substring('ABCDEFGHJKLMNPQRSTUVWXYZ',(abs(checksum(newid())) % 24)+1, 1)+
substring('ABCDEFGHJKLMNPQRSTUVWXYZ',(abs(checksum(newid())) % 24)+1, 1)+
substring('ABCDEFGHJKLMNPQRSTUVWXYZ',(abs(checksum(newid())) % 24)+1, 1)+
substring('ABCDEFGHJKLMNPQRSTUVWXYZ',(abs(checksum(newid())) % 24)+1, 1) unq
FROM table) x
GROUP BY unq
HAVING COUNT(*) > 1
ORDER BY 2 DESC

Select allOpen in new window

You have two options:
- create a unique index on that field and update the table row by row in a TRY - CATCH block
- create a list of unique codes and update the table from this list

Thanks for the feedback, but field does not have to be unique in the instance.

It would be useful if I needed it!

LOL, than I didn't understand the sentence
"update a field for every row in a table with a different random 4 letter uppercase string".

Apologies!

Just an FYI, but if you need a unique value for each row (hence my question about how many rows you'll need) Prime Numbers are your friend.

If the table has an IDENTITY column, use that, otherwise generate a unique number for each row with row_number ();

Now pick a number.  Any number.  If you've got a lucky number, use it.

Then pick a prime number.  Any prime number (less than the number of rows).  3 works.  So does 7, 11, 13, 9967, etc.

For each row, your key will be BaseNumber + PrimerNumber * RN (row number);

Then using technique similar to what you did, convert the number to a string of characters.

mod(BaseNumber + PrimerNumber * RN (row number), 24)

mod(trunc(BaseNumber + PrimerNumber * RN (row number))/24, 24)

mod(trunc(BaseNumber + PrimerNumber * RN (row number))/(24*2), 24)

mod(trunc(BaseNumber + PrimerNumber * RN (row number))/(24*3), 24)

Good Luck!

Kent

Why to do it so complex? Unique values are not required, so the update could look like this:

update table set field = 'ABCD'

Select allOpen in new window

:-)

@kent
Beside the fact we are not in Oracle, the following query running on a 16K rows on SQL Server table produces 1152 unique rows. What are the correct prime and lucky numbers?

DECLARE @prime INT = 3, @base INT = 11
SELECT DISTINCT c1, c2, c3, c4 FROM (
SELECT ROW_NUMBER() OVER (ORDER BY pfcID) RN, 
(@base + @prime * ROW_NUMBER() OVER (ORDER BY @base)) % 24 c1, 
(@base + @prime * ROW_NUMBER() OVER (ORDER BY @base)/24) % 24 c2, 
(@base + @prime * ROW_NUMBER() OVER (ORDER BY @base)/48) % 24 c3, 
(@base + @prime * ROW_NUMBER() OVER (ORDER BY @base)/72) % 24 c4
FROM Some16000rowsTable
) x

Select allOpen in new window

Apologies if I mislead you with the question, but I don't need it to be unique, just "different".

I get a unique field by using a computed field with the hex of the record id concatenated with the field I generate.

It works and is simple! 

Hi Richard,

I understood that.  :)  These kinds of requirements can easily change and I was offering a head start if you needed that.  I'm certainly not trying to get you to change something that works for you!!!

Hi Pavel,

That was just skeleton code, to provide the starting index into the string for each of the 4 "digits".  RN is, of course the IDENTITY column value or generated with row_number().  And I did get into a bit of a hurry when I typed that previous response.  The value of BaseNumber needs to be larger than the number of rows.

This will produce 4 numbers in the range of 0 to 23, with each row being unique from all of the others.  The generated values can be used to index into the string 'ABCDEFGHJKLMNPQRSTUVWXYZ'.

DECLARE @PrimeNumber INT = 37, @BaseNumber INT = 20000
SELECT 
  mod(@BaseNumber + @PrimeNumber * RN, 24) A,
  mod(@BaseNumber + trunc(@PrimeNumber * RN/24), 24) B,
  mod(@BaseNumber + trunc(@PrimeNumber * RN/(24*2)), 24) C,
  mod(@BaseNumber + trunc(@PrimeNumber * RN/(24*3)), 24) D
FROM Some16000rowsTable;

Select allOpen in new window