How can we write a SQL function to increment alphabetic 'sequence_code'

Question

0.00/5 (No votes)

See more:

SQL-Server-2016

How can we Write a SQL function to Increment Alphabetic 'Sequence_Code' for only those ID whose Status is'A'
Alphabetic 'Sequence_Code' is combination of Last Two Digit of 'Current_Year' and 2 character alphabet like AA,AB,AC....AZ, BA,BB,BC...BZ. CA,CB,CC....

My Table 'LoadData' is as below:

Sequence_Code |     ID | Current_Year | Record_Date| Status     |
 :------------ | -----: | -----------: | :----------|          --|
 17AA          | 310001 |         2017 | 2017-01-01 | S
 18AB          | 310002 |         2018 | 2018-02-22 | S
 19AC          | 310003 |         2019 | 2019-02-10 | S
 20AD          | 310004 |         2019 | 2019-02-20 | A
 20AE          | 310005 |         2020 | 2020-03-20 | S
 NULL          | 310006 |         2020 | 2020-04-20 | A

IF Last Sequence_Code is haveing AZ(In last 2 char), then from next ID it should generate like BA, BB..and so on..

Expected Output is:

Sequence_Code |     ID | Current_Year | Record_Date| Status     |
 :------------ | -----: | -----------: | :----------|          --|
 17AA          | 310001 |         2017 | 2017-01-01 | S
 18AB          | 310002 |         2018 | 2018-02-22 | S
 19AC          | 310003 |         2019 | 2019-02-10 | S
 20AD          | 310004 |         2019 | 2019-02-20 | A
 20AE          | 310005 |         2020 | 2020-03-20 | S
 20AF          | 310006 |         2020 | 2020-04-20 | A

Please suggest if there is any convenient way..

What I have tried:

I tried the below code,But i could not write a proper logic, so it's not working.

SQL

 UPDATE LoadData
SET Sequence_Code = SUBSTRING(MAX(Sequence_Code ),1,3)+ CHAR(ASCII(SUBSTRING(MAX(Sequence_Code ),3,4))+1)
Where Status='I' AND Sequence_Code is NULL

Posted 4-Jan-21 1:11am

Member 15036870

Updated 4-Jan-21 1:58am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2021-01-04T01:56:00

Please, take a look here: Custom Auto-Generated Sequences with SQL Server - SQLTeam.com[^]

There you'll find few very interesting functions with detailed information about algorithm.

All what you need to do is to get one of existing functions and to change to your needs.
Here is an idea:

SQL

;WITH CTE AS
(
    SELECT 0 CurrNo
    UNION ALL
    SELECT CurrNo+1
    FROM CTE
    WHERE CurrNo+1<100
)
SELECT CurrNo, char(CurrNo /26 % 26 + 65) + char(CurrNo  % 26 + 65) CurrCode
FROM CTE

Result:

CurrNo 	CurrCode
0 	AA
1 	AB
2 	AC
3 	AD
4 	AE
5 	AF
6 	AG
7 	AH
8 	AI
9 	AJ
10 	AK
11 	AL
12 	AM
13 	AN
...
96 	DS
97 	DT
98 	DU
99 	DV

SQL Server 2019 | db<>fiddle[^]

So, to get digit representation od code, you need to convert it:

SQL

;WITH InitData AS
(
  SELECT '20AZ' Sequence_Code
)
SELECT a.*, char((SumOfDigits +1) /26 % 26 + 65) + char((SumOfDigits +1) % 26 + 65) NewCode
FROM 
(
  SELECT Sequence_Code, LEFT(Sequence_Code, 2) CurrentYear, 
    RIGHT(Sequence_Code, 2) Curr_Code, 
    ASCII(SUBSTRING(Sequence_Code, 3, 1))-65 FirstDigitOfCode, 
    ASCII(RIGHT(Sequence_Code, 1))-65 SecondDigitOfCode,
    ASCII(SUBSTRING(Sequence_Code, 3, 1))-65  +
    ASCII(RIGHT(Sequence_Code, 1))-65 SumOfDigits
  FROM InitData
) a

Result:

Sequence_Code 	CurrentYear 	Curr_Code 	FirstDigitOfCode 	SecondDigitOfCode 	SumOfDigits 	NewCode
20AZ 			20		 	AZ 			0		 	25 			25	 	BA

db<>fiddle#2[^]

Good luck!

How can we write a SQL function to increment alphabetic 'sequence_code'

1 solution

Solution 1

Add your solution here

Preview 0

How can we write a SQL function to increment alphabetic 'sequence_code'

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us