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:24:36
|
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
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2015-04-21 : 14:23:00
|
[code]DECLARE @Alphas TABLE (alpha CHAR(1))
INSERT INTO @Alphas
select char(number)
from master..spt_values
where number between 65 and 90 and type = 'P'
and number not in (73,79)
DECLARE @Numbers TABLE (number CHAR(1))
INSERT INTO @numbers
VALUES ('0'), ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
select a1.alpha+ a2.alpha +n1.number+n2.number +a3.alpha+a4.alpha+n3.number+n4.number
from @Alphas a1
cross join @alphas a2
cross join @numbers n1
cross join @numbers n2
cross join @Alphas a3
cross join @alphas a4
cross join @numbers n3
cross join @numbers n4
[/code]
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
|
|
|
|
|
Debug fn asap-Generate 8char alphanumeric Seq No