June 30, 2015 at 10:20 am
Hi,
I need to generate unique string Id from the range AAAAA to PZZZZ. This id will be unique for each Users for e.g.
User ---- TransactionNo ---- UniqueId
--------------------------------------------
12001 --- 101 --- AAAAA
12001 ---- 102 --- AAAAB
12001 --- 103 --- AAAAC
12001 ---- 104 --- AAAAD
13001 --- 105 --- AAAAA
13001 ---- 106 --- AAAAB
13001 --- 107 --- AAAAC
13001 ---- 108 --- AAAAD
more the records of each user, unique should be the ID as displayed above
Please let me know the optimized way to generate the same
Thanks & Regards,
Saumik
June 30, 2015 at 10:40 am
this is usually frowned upon, because the unique string generated can (and eventually Will) end up being an offensive word. there's no need to show a string, when a number or a GUID would do just fine.
Lowell
June 30, 2015 at 10:47 am
Lowell (6/30/2015)
this is usually frowned upon, because the unique string generated can (and eventually Will) end up being an offensive word. there's no need to show a string, when a number or a GUID would do just fine.
Indeed, that's always an awkward conversation when you're giving out random strings to customers and a less than appropriate string comes up.....
June 30, 2015 at 11:59 am
First... Let me say that I agree 100% with Lowel's & ZZartin's comments. I think what you're attempting is a bad idea...
That said, none of us work where you work or have the bosses that you have. Some bosses insist on moving forward with bad ideas and your paycheck depends on making them happen no matter what your personal opinion is on the matter. So... With that in mind, here's a way to do it...
Start by making a permanent table to hold all 7.3 million + combinations. (generating in the fly will get expensive quick) Plus, it'll give you the chance to remove any objectionable combinations before they cause a "situation".
WITH Alphas AS (
SELECT Chr FROM (VALUES ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) x (chr)
)
SELECT
ISNULL(ROW_NUMBER() OVER (ORDER BY a1.Chr,a2.Chr,a3.Chr,a4.Chr,a5.Chr), 0) AS RN,
CAST(a1.Chr + a2.Chr + a3.Chr + a4.Chr + a5.Chr AS CHAR(5)) AS ChrString
INTO #AvailableStringIDs -- Use a perminant table instead of a temp table
FROM
Alphas a1, Alphas a2, Alphas a3, Alphas a4, Alphas a5
WHERE
a1.chr BETWEEN 'A' AND 'P';
ALTER TABLE #AvailableStringIDs ADD CONSTRAINT pk_AvailableStringIDs_RN PRIMARY KEY CLUSTERED (RN)
Once you have that it's just a simple matter of attaching them to the individual transactions...
-- Some test data --
IF OBJECT_ID('tempdb..#Transactions') IS NOT NULL
DROP TABLE #Transactions;
CREATE TABLE #Transactions (
UserID INT,
TransactionID INT
);
INSERT #Transactions (UserID,TransactionID) VALUES
(12001,101),
(12001,102),
(12001,103),
(12001,104),
(13001,105),
(13001,106),
(13001,107),
(13001,108);
-- The actual solution --
WITH Transactions AS (
SELECT
t.UserID,
t.TransactionID,
ROW_NUMBER() OVER (PARTITION BY t.UserID ORDER BY t.TransactionID) AS RN
FROM
#Transactions t
)
SELECT
t.UserID,
t.TransactionID,
asid.ChrString
FROM
Transactions t
JOIN #AvailableStringIDs asid
ON t.RN = asid.RN
The results...
UserID TransactionID ChrString
----------- ------------- ---------
12001 101 AAAAA
12001 102 AAAAB
12001 103 AAAAC
12001 104 AAAAD
13001 105 AAAAA
13001 106 AAAAB
13001 107 AAAAC
13001 108 AAAAD
HTH,
Jason
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply