Automatically generate unique id like UID 22-001

Question

I want to automatically generate unique id with per-defined code attach to it. ex:

'UID 22-001..
'UID 22-002..
'UID 22-003 ('22' is year 2022) 

and then when the year is 2023 it will be generated as;

'UID 23-001..
'UID 23-002..
'UID 23-003..

and so on. Thanks in advance for the help.

Answer 1

Consider you have table with this columns:

id varchar(max),
name varchar(20),
address varchar(20))

You are going to insert value with the ID format that you mentionned.

Here is an example query:

INSERT INTO TABLE_NAME
VALUES(
(SELECT concat('UID ' , (SELECT RIGHT(YEAR(getdate()),2)),'-',(select RIGHT('000'+(CAST(MAX(CAST(RIGHT(ID,2) AS INT) + 1) AS NVARCHAR )),3) FROM TABLE_NAME))),'RAM','INDIA')