Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nanditha
Starting Member
4 Posts |
 Posted - 2015-04-20 : 22:30:27
|
Please Help - GENERATE 8 CHARACTER ALPHANUMERIC SEQUENCES
Requirements
• ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99
Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
• Generate from Alphabets – A through Z Numbers -0 to 9
• Generate Unique Sequence (No Duplicates).
• Must Eliminate letters I and O
Output Expected
• AA00AA00………..ZZ99ZZ99
• Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records
Below is my Sql Function -
CREATE function [dbo].[SequenceComplexNEW]
(
@Id BIGINT
)
Returns char(8)
AS
BEGIN
DECLARE @OUT AS CHAR(8)--,@Id as Bigint
WHILE
char(@Id / power(26,3) % 26 + 65) between char(65) and char(90)
and char(@Id / power(26,2) % 26 + 65) between char(65) and char(90)
and char(@Id / 26 % 26 + 65) between char(65) and char(90)
and char(@Id % 26 + 65) >= char(65) and char(@Id % 26 + 65) <= char(90)
--and char(@Id / power(26,3) % 26 + 65) != char(73)
--and char(@Id / power(26,2) % 26 + 65) != char(73)
--and char(@Id / 26 % 26 + 65) != char(73)
--and char(@Id / power(26,3) % 26 + 65) != char(79)
--and char(@Id / power(26,2) % 26 + 65) != char(79)
--and char(@Id / 26 % 26 + 65) != char(79)
--and char(@Id % 26 + 65) != char(79)
and char(@Id/power(10,3)%10 + 48) between char(48) and char(57)
and char(@Id/power(10,2)%10 + 48) between char(48) and char(57)
and char(@Id/power(10,1)%10 + 48) between char(48) and char(57)
and char(@Id%10+48) between char(48) and char(57)
BEGIN
SET @OUT =
char(@Id/power(26,3)%26 + 65)
+char(@Id/power(26,2)%26 + 65)
+char(@Id/power(10,3)%10 + 48)
+char(@Id/power(10,2)%10 + 48)
+char(@Id/power(26,1)%26 + 65)
+Case WHEN char(@Id % 26 + 65) >= char(73) THEN char(@Id % 26 + 66) -- WHEN char(@Id % 26 + 65) >= char(79) THEN char(@Id % 26 + 66)
Else char(@Id%26 + 65) END
--+char(@Id % 26 + 65)
+char(@Id/power(10,1)%10 + 48)
+char(@Id%10+48)
IF char(@Id % 26 + 65) > char(90)
BEGIN
BREAK
END
ELSE
CONTINUE
--Print @out
END
RETURN @OUT
END
GO
|
|
|
Nanditha
Starting Member
4 Posts |
Posted - 2015-04-20 : 22:37:00
|
| test |
 |
|
|
Kristen
Test
22859 Posts |
|
|
Nanditha
Starting Member
4 Posts |
Posted - 2015-04-21 : 09:23:26
|
quote:
Originally posted by Kristen
Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=200917
Please do not double-post on this forum
Sure .. Thanks.
Any answers please
Adding While loop and break is causing infinite looping..
I want only unique sequences and no duplicates .Also only for 24 letters ignoring letter I and O.
PLEASE HELP ! THANKS |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2015-04-22 : 10:32:41
|
I answered this yesterday in the New to SQL Server forum.
Jim
Everyday I learn something that somebody else already knew |
|
|
|
|
|
|
Need Help - create 8 chaAlphanumeric sequence