Help needed- Generate 8 char Alphanumeric Unique Sequence

  • 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

  • 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

    This should be done in a programming language and not in t-sql or even better not at all. The requirements here are quite unique and not something that you see with t-sql often, if at all. What is the real world usage for this requirement?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I need to load the output in database table.so I am trying to write a Sql stored procedure or function to return the sequence numbers and load the sequence numbers to the table using SSIS.

  • ditha (4/21/2015)


    I need to load the output in database table.so I am trying to write a Sql stored procedure or function to return the sequence numbers and load the sequence numbers to the table using SSIS.

    That explains the process but what is the purpose of the rules around the values of this sequence? There are many other ways to generate unique values that are a LOT simpler (identity, sequence, guid)

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi, Thanks for the immediate Suggestion.

    I am using below as Input query to load sequence records to destination table

    SELECT ID, [Database].[dbo].[SequenceComplexNEW](ID) AS Sequence

    FROM (SELECT n AS ID FROM Database.dbo.ufn_Nums(26))ID

    below is the ID Function -Database.dbo.ufn_Nums(3317760000))ID

    CREATE FUNCTION [dbo].[ufn_Nums](@n AS BIGINT) RETURNS TABLE

    AS

    RETURN

    WITH

    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

    L1 AS(SELECT 1 AS C FROM L0 AS A, L0 AS B),

    L2 AS(SELECT 1 AS C FROM L1 AS A, L1 AS B),

    L3 AS(SELECT 1 AS C FROM L2 AS A, L2 AS B),

    L4 AS(SELECT 1 AS C FROM L3 AS A, L3 AS B),

    L5 AS(SELECT 1 AS C FROM L4 AS A, L4 AS B),

    Nums AS(SELECT ROW_Number() OVER(ORDER BY c) AS n FROM L5)

    SELECT n FROM Nums WHERE n <= @n;

    GO

  • ditha (4/21/2015)


    Hi, Thanks for the immediate Suggestion.

    I am using below as Input query to load sequence records to destination table

    SELECT ID, [Database].[dbo].[SequenceComplexNEW](ID) AS Sequence

    FROM (SELECT n AS ID FROM Database.dbo.ufn_Nums(26))ID

    below is the ID Function -Database.dbo.ufn_Nums(3317760000))ID

    CREATE FUNCTION [dbo].[ufn_Nums](@n AS BIGINT) RETURNS TABLE

    AS

    RETURN

    WITH

    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

    L1 AS(SELECT 1 AS C FROM L0 AS A, L0 AS B),

    L2 AS(SELECT 1 AS C FROM L1 AS A, L1 AS B),

    L3 AS(SELECT 1 AS C FROM L2 AS A, L2 AS B),

    L4 AS(SELECT 1 AS C FROM L3 AS A, L3 AS B),

    L5 AS(SELECT 1 AS C FROM L4 AS A, L4 AS B),

    Nums AS(SELECT ROW_Number() OVER(ORDER BY c) AS n FROM L5)

    SELECT n FROM Nums WHERE n <= @n;

    GO

    Holy cow!!! How many rows do you anticipate in your load? This is a ridiculous amount of rows. This is 4.2 billion rows. This is going to be hideously slow no matter what you do. A scalar function for this is going to melt your sql server and render it useless for days. Why not just use an identity on a bigint column? It would be faster by hours and hours.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Eiminating I and O - its needs to return 24*24*10*10*24*24*10*10 exactly 3 317760000 records .

    That's fine with the data load if its slow.

    but I need to get my function working and return this exact results.

    Below is sample Output- >

    AA00AA00

    AA00AB01

    AA00AC02

    --

    AA00AH08

    AA00AJ09

    ---

    AA00AN13

    AA00AP14

    --

    AA00BA01

    ---

    ZZ99ZZ99

    MY CODE WORKS. BUT CODE NEEDS TO MODIFIED TO PUT IN A LOOP WHICH GENERATES ONLY FROM A TO Z AND 0 TO 9 ALL 8 COMBINATIONS.

    NO GENERATING DUPLICATES

    AND SHOULD ELIMINATE LETTER I AND O...

  • ditha (4/21/2015)


    Eiminating I and O - its needs to return 24*24*10*10*24*24*10*10 exactly 3 317760000 records .

    That's fine with the data load if its slow.

    but I need to get my function working and return this exact results.

    Below is sample Output- >

    AA00AA00

    AA00AB01

    AA00AC02

    --

    AA00AH08

    AA00AJ09

    ---

    AA00AN13

    AA00AP14

    --

    AA00BA01

    ---

    ZZ99ZZ99

    MY CODE WORKS. BUT CODE NEEDS TO MODIFIED TO PUT IN A LOOP WHICH GENERATES ONLY FROM A TO Z AND 0 TO 9 ALL 8 COMBINATIONS.

    NO GENERATING DUPLICATES

    AND SHOULD ELIMINATE LETTER I AND O...

    This requirement is still quite strange. I would love to hear the actual usage of such a combination.

    Why not just generate 2 ctes, one with A - Z (except I and O) and another with 0 - 9. Then do some cross joins.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks ! Can you please post the code if possible ?

  • ditha (4/21/2015)


    Thanks ! Can you please post the code if possible ?

    Just like you did above but instead of having your cte be 2 rows, make the table the values you want.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Can you please please please recode and post and I shall look into and modify or test ?

    Thanks for the great help

  • ditha (4/21/2015)


    Can you please please please recode and post and I shall look into and modify or test ?

    Thanks for the great help

    I am a bit scared that you are wanting somebody to create code for you that is very similar to what you already posted. That is a really good indication that you don't understand the cte you posted previously. I will show you a couple lines but I am NOT going to provide you a complete solution with you putting in no effort.

    I will ask for a third time. What is the reason you need to use this sequence? I am truly curious about that because the logic is very strange and I would like to understand what business role such a thing might solve.

    Here is the first part of your cte...

    with Alpha (Value) as

    (

    select 'A' union all

    select 'B' union all

    Then add a second cte for your numbers 0 - 9. The cross joins from there should be pretty self evident.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I'm with Sean - this is a bizarre requirement. This will get you started...

    WITH

    E1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1)) t(N)),

    E2 AS (SELECT TOP 26 N = ROW_NUMBER() OVER (ORDER BY (SELECT (NULL))) FROM E1 a, E1 b),

    CH AS (SELECT C = CHAR(N+64) FROM E2),

    T AS (SELECT TOP 10 N = N-1 FROM E2)

    SELECT

    CAST(c0.C AS char(1))+

    CAST(c1.C AS char(1))+

    CAST(t0.N AS char(1))+

    CAST(t1.N AS char(1))

    FROM T t0, T t1, CH c0, CH c1;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • thanks This looks helpful .. I need to generate sequence with next 4 alpha numbers - aa00ab01,aa00ac02,

    can you please help me with the complete/full output code ...

    Thanks loads

  • Sorry if u misunderstood..

    I did get yor CTE and able to work on it ...

    I am inbetween working on my code and testing it.. just posted meanwhile..

    Sorry again if u misunderstood . I definitely will work on CTE and test it and I Didn't mean not to effort .. again ..

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply