?
Solved

Generating a unique Order Confirmation Code

Posted on 2005-05-16
4
Medium Priority
?
412 Views
Last Modified: 2008-02-01
I would like to have SQL generate a unique confirmation code, can use both numbers and letters and would like the length to be 12 character.  I could have coldfusion generate this, but think it would be better if sql did.  What code could i use to generate this as a dafault value generated or a trigger/stored procedure.
0
Comment
Question by:polobruce
  • 2
4 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 14011849
0
 
LVL 11

Expert Comment

by:andrewbleakley
ID: 14014329
you could try something like this (it was originally for random passwords but will work)

DECLARE @order_confirmation varchar(12), @type tinyint, @bitmap char(6), @len int
SET @len = 12
SET @order_confirmation=''

WHILE @len > 0
BEGIN
      
            SET @type = ROUND(1 + (RAND() * (3)),0)

            IF @type = 1 --Appending a random lower case alphabet to @order_confirmation
                  SET @order_confirmation = @order_confirmation + CHAR(ROUND(97 + (RAND() * (25)),0))
            ELSE IF @type = 2 --Appending a random upper case alphabet to @order_confirmation
                  SET @order_confirmation = @order_confirmation + CHAR(ROUND(65 + (RAND() * (25)),0))
            ELSE IF @type = 3 --Appending a random number between 0 and 9 to @order_confirmation
                  SET @order_confirmation = @order_confirmation + CHAR(ROUND(48 + (RAND() * (9)),0))
            ELSE IF @type = 4 --Appending a random special character to @order_confirmation
                  SET @order_confirmation = @order_confirmation + CHAR(ROUND(33 + (RAND() * (13)),0))

            SET @len = @len - 1

END

PRINT @order_confirmation
/************
if (select count(*) from orders where confirmation_id = @order_confirmation) <> 0
begin
      --run again
end
else
begin
      -- here you return your @order_confirmation
end
************/

-- modified from http://vyaskn.tripod.com/code/password.txt
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 14014844
I suggest creating an IDENTITY value using a BIGINT, with an appropriate seed value, then using a function (provided below) to convert that BIGINT into a hex string.  For example:

CREATE TABLE ... (
    confirmationNumber BIGINT IDENTITY(18272165346984, 1),
    ...,
    confirmationCode AS dbo.ConvertToHex6(confirmationNumber)
)
--NOTE: 18272165346984 = x'109E51ECBEA8'

When you insert into the table, the confirmation number will be generated automatically; you can automatically convert that number to the code whenever needed just by using the column name "confirmationCode".

SELECT confirmationCode, ...
FROM ...
...


IF OBJECT_ID ('ConvertToHex6') IS NOT NULL
  DROP FUNCTION dbo.ConvertToHex6
GO
CREATE FUNCTION dbo.ConvertToHex6 (
      @value BIGINT
)
RETURNS CHAR(12)
AS
BEGIN
DECLARE @bin BINARY(6)
DECLARE @binChars CHAR(16)

SET @bin = @value
SET @binChars = '0123456789ABCDEF'

RETURN SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 1, 1)) / 16 + 1, 1) +
      SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 1, 1)) % 16 + 1 , 1) +
      SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 2, 1)) / 16 + 1, 1) +
      SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 2, 1)) % 16 + 1 , 1) +
      SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 3, 1)) / 16 + 1, 1) +
      SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 3, 1)) % 16 + 1 , 1) +
      SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 4, 1)) / 16 + 1, 1) +
      SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 4, 1)) % 16 + 1 , 1) +
      SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 5, 1)) / 16 + 1, 1) +
      SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 5, 1)) % 16 + 1 , 1) +
      SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 6, 1)) / 16 + 1, 1) +
      SUBSTRING(@binChars, ASCII(SUBSTRING(@bin, 6, 1)) % 16 + 1 , 1)
END --FUNCTION
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 14126261
Thanks!

Instead of a computed column, you could also, of course, simply store the confirmationCode as a "real"/physical column, using, for example, a trigger to do the conversion to hex *once* when the row is inserted, and using the already-converted column after that.  It's a trade off: the computed column uses more CPU and memory, the physical column more disk.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

864 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