April 20, 2015 at 8:53 pm
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
April 21, 2015 at 7:27 am
ditha (4/20/2015)
Please Help - GENERATE 8 CHARACTER ALPHANUMERIC SEQUENCESRequirements
• 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/
April 21, 2015 at 7:44 am
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.
April 21, 2015 at 7:47 am
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/
April 21, 2015 at 7:55 am
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
April 21, 2015 at 8:10 am
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/
April 21, 2015 at 8:19 am
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...
April 21, 2015 at 8:29 am
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/
April 21, 2015 at 8:52 am
Thanks ! Can you please post the code if possible ?
April 21, 2015 at 8:54 am
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/
April 21, 2015 at 9:03 am
Can you please please please recode and post and I shall look into and modify or test ?
Thanks for the great help
April 21, 2015 at 9:12 am
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/
April 21, 2015 at 9:15 am
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;
-- Itzik Ben-Gan 2001
April 21, 2015 at 9:27 am
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
April 21, 2015 at 9:35 am
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