asked on

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

I need to build a sql query for MSSQL that will update a field for every row in a table with a different random 4 letter upper case string, that excluded I and O, EG BHGR.

Any help would be greatly appreciated.

Is there an event that triggers it?

**No credit card required.**

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
```

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

ASKER

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

It would be useful if I needed it!

"update a field for every row in a table with a

**different**random 4 letter uppercase string".

ASKER

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

`update table set field = 'ABCD'`

:-)
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
```

ASKER

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;

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.