ditha (4/20/2015)

---

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