Need Help - create 8 chaAlphanumeric sequence

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Need Help - create 8 chaAlphanumeric sequence

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

Posted - 2015-04-21 : 05:56:57

Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=200917

Please do not double-post on this forum

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources