Create random ID Unique

AIdoHSG
AIdoHSG used Ask the Experts™
on
Hello,
I have a table with two columns (RecordName, Id). I'm trying to create an 8 Character ID unqiue for each record using the characters A-Z (except for the letters L,O,I). The characters will need to be random and unique (the Ids should be executed using a stored procedure).

I'm not sure if I can create an array and loop through it for each record...
Does anyone have any suggestions?

I started by creating a temp table with the letters in there, but then I wasn't sure how to do the rest or if it should be done differently. I would appreciate the input.

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
Why do u need a character Unique id, you can use uniqueidentifier column

select newId () always gives you unique values

Commented:
UPDATE MyTable
SET ID = Select LEFT(REPLACE(REPLACE(REPLACE(REPLACE(NewID(), '-',''), 'L',''),'O',''),'I',''),8)
But the keys will have numbers. if you do not want it you can use replace to get rid of numbers as well.
P.

Author

Commented:
The characters that are created represent projects for studies in-house which this id will be later incoporated into Other project Ids. I need to make sure that the ID created is 8 characters in lenght and is combination of letters A-Z except for L,O and I. Each record must have these unique strand of characters.

passandhu, I tried your code and I got an error:
Incorrect Syntax near the Keyword 'Select'.

the user will be able to request this for only certain records, but I wanted to make sure that i can create this strand of characters before moving to the next phase.

I would appreciate the assistance thx.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Commented:

UPDATE MyTable
SET ID =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NewID(), '-',''), 'L',''),'O',''),'I',''),'0', ''),'1', ''),'2', ''),'3', ''),'4', ''),'5', ''),'6', ''),'7', ''),'8', ''),'9', '')
Alright so this will do what you are looking for.
Hope this helps.
P.
Top Expert 2012
Commented:
Here wrap this is in a function:
Declare @T table (C tinyint)
Declare @C tinyint,
            @Value varchar(8)

SET @C = 48
While @C <= 90
      Begin
            IF (@C BETWEEN 48 AND 57 Or @C BETWEEN 65 And 90) And @C Not In (73, 76, 79)
                  INSERT @T (C) Values (@C)
            
            SET @C = @C + 1
      END


Set @Value = ''
Select      TOP 8
            @Value = @Value + CHAR(C)
FROM      @T
ORDER By
            NEWID()

Select @Value

Author

Commented:
Thank you acperkins...
Would you be able to just explain to me what is happening in the syntax... just for my own benefits.

thank you again for your help
Top Expert 2012

Commented:
>>Would you be able to just explain to me what is happening in the syntax... just for my own benefits.<<
First of all I was not entirely serious when I posted that.  I do not believe that T-SQL it the right place to do that.  That is entirely a presentation layer problem that is best addressed with something like .NET.

But to answer your question here is what it does:
1. Inserts into a table all the valid values.
2. Picks 8 values at random ( NEWID() )
3. Concatenates them into a string.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial