Auto generate AlphaNumeric with defined letters and numbers (e.g. A1-A6)

Question

Post reply

Auto generate AlphaNumeric with defined letters and numbers (e.g. A1-A6)

spungus

SSC-Addicted

Points: 443
More actions
September 29, 2020 at 10:23 am
Go to Answer
#3791557
Hi to All,
This is the problem, I need to generate a sequence AlphaNumeric characters based on certain condition.
For example, the requirement is 6x3 see below output.
The next alphanumeric is B3. Once it become B6 the next is C1-C6 only, it cannot exceed to next
D1 because its only 6x3. If the requirement is 6x4 then the data is up to D1-D6. The FK is from the another table
which i supply during insert command. It is the FK column that group the output.
```
--Master Table 1 6x3
SELECT 101 as ID,'6x3' as Box

--Detail Table 2
SELECT 1 as ID,101 as FK, 'A1' as Mark
UNION ALL
SELECT 2 as ID,101 as FK, 'A2' as Mark
UNION ALL
SELECT 3 as ID,101 as FK, 'A3' as Mark
UNION ALL
SELECT 4 as ID,101 as FK, 'A4' as Mark
UNION ALL
SELECT 5 as ID,101 as FK, 'A5' as Mark
UNION ALL
SELECT 6 as ID,101 as FK, 'A6' as Mark
UNION ALL
SELECT 7 as ID,101 as FK, 'B1' as Mark
UNION ALL
SELECT 8 as ID,101 as FK, 'B2' as Mark
```
Thanks in advance!

DesNorton

SSC-Insane

Points: 24448

September 30, 2020 at 4:22 am

Answer

#3791792

This will give you only the marks that have not been used. Use the ORDER BY to get them in order. If there is nothing returned, then the box is full.

DECLARE @MasterID   int     = 101;

DECLARE @NumNumbers tinyint = 6;
DECLARE @NumLetters tinyint = 3;

;WITH H2  (N) AS ( SELECT 1 FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ) AS V(N)) -- 32 rows
    , NUMS(N) AS (SELECT TOP(@NumNumbers) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM H2)
    , LETS(N) AS (SELECT TOP(@NumLetters) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM H2)
SELECT
      FK   = @MasterID
    , Mark = CHAR(64+l.N) + CONVERT(varchar(2), N.N)
FROM NUMS AS N
CROSS JOIN LETS AS L
WHERE NOT EXISTS (SELECT 1 FROM [DetailTable] AS d
                  WHERE d.FK = @MasterID
                    AND d.Mark = CHAR(64+l.N) + CONVERT(varchar(2), N.N)
                  )
ORDER BY L.N, N.N
OPTION (RECOMPILE);

You need to adapt the code to fit your circumstances.

How to post data/code on a forum to get the best help.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004498 More actions · Answer 1

What is the max? Z6? I ask because that will mean that you have a total capacity of 26*6 or 156 unique values before you run out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spungus SSC-Addicted Points: 443 More actions · Answer 2

Thanks for the question Jeff.

For now we only have, 6x3,6x4,6x5 and 8x5 and 8x6.

So the MAX is F6 and F8 and the Z6 will not happen.

DesNorton SSC-Insane Points: 24448 More actions · Answer 3

spungus wrote:

Thanks for the question Jeff.
For now we only have, 6x3,6x4,6x5 and 8x5 and 8x6.
So the MAX is F6 and F8 and the Z6 will not happen.

The number of times that people get caught with their pants down because "We only have to cater for ..."

Anyway, this should cater for your immediate requirements.

DECLARE @NumLetters tinyint = 6;
DECLARE @NumNumbers tinyint = 8;

;WITH H2  (N) AS ( SELECT 1 FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ) AS V(N)) -- 32 rows
    , NUMS(N) AS (SELECT TOP(@NumNumbers) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM H2)
    , LETS(N) AS (SELECT TOP(@NumLetters) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM H2)
SELECT
    AlphaNumeric = CHAR(64+l.N) + CONVERT(varchar(2), N.N)
FROM NUMS AS N
CROSS JOIN LETS AS L
--ORDER BY L.N, N.N
OPTION (RECOMPILE);

How to post data/code on a forum to get the best help.

Sergiy SSC Guru Points: 110208 More actions · Answer 4

All you need is to convert normal numerical sequence to desired codes.

Something like this:

declare @a tinyint, @b tinyint, @t smallint 
select @a = 6, @b = 4, @t = @a*@b 
SELECT STR(@a, 1) + 'x' + STR(@B, 1) as Box

select N, CHAR(N/@a + 65) + STR( N%@a + 1, 1) as Mark
from dbo.TallyGenerator (0, null, @t, 1)

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004498 More actions · Answer 5

Sergiy wrote:

All you need is to convert normal numerical sequence to desired codes.

Something like this:

declare @a tinyint, @b tinyint, @t smallint 
select @a = 6, @b = 4, @t = @a*@b 
SELECT STR(@a, 1) + 'x' + STR(@B, 1) as Box

select N, CHAR(N/@a + 65) + STR( N%@a + 1, 1) as Mark
from dbo.TallyGenerator (0, null, @t, 1)

Sergiy... if you're going to use the dbo.tallyGenerator code, you need to provide a link for it. You might want to add the link to your signature line like I did for dbo.fnTally.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spungus SSC-Addicted Points: 443 More actions · Answer 6

Thanks for the answer DesNorton & Sergiy

I already test your code DesNorton and it's working when generating all alpha numeric, but the current process or insert is one at a time only, because of waiting time for other items to finish, once finished they can assign a MARK value.

That is why i need to know the last insert, in my sample it's B2. Also in this problem i need to know if the Box 6x4 are already full or completed.

I'm very sorry if my question/requirements is not very clear.

Sergiy, im not able to run the code because of missing Tally function, please provide the function script.

TIA!

Sergiy SSC Guru Points: 110208 More actions · Answer 7

spungus wrote:

Sergiy, im not able to run the code because of missing Tally function, please provide the function script.
TIA!

Here it is:

TallyGenerator

This reply was modified 4 years, 4 months ago by Sergiy.
This reply was modified 4 years, 4 months ago by Sergiy.

_____________
Code for TallyGenerator

spungus SSC-Addicted Points: 443 More actions · Answer 8

Thank you Sergiy and DesNorton for the help!

DesNorton code solve all my problem i just change a little bit.

Thanks again!

jcelko212 32090 SSCrazy Eights Points: 9304 More actions · Answer 9

>> I need to generate a sequence AlphaNumeric characters based on a certain condition. <<

You talk about a sequence, but that's how filesystems work. Perhaps you meant to think about generating a set?

>> For example, the requirement is 6x3 see below output.<<

SELECT *

FROM ((VALUES ('A', 'B', 'C'))

CROSS JOIN

(VALUES('1', '2', '3', '4','5','6')))

Please post DDL and follow ANSI/ISO standards when asking for help.