David David - 2 months ago 7
SQL Question

SQL, I need make a number xx21xxxx

My question is, if i can add a column with default 21,
First 2 numbers can be 00-99
The next 2 numbers always will be 21
And the last numbers can be 0000-9999

For example if im entrance in university in the year 16 ther fisrt 2 numers are 16

The next 2 number will be 21

The last 4 numbers iniciate in 0000

The result is 16 21 0000
If i add other, 16 21 0001.

Answer

You can use computed column to generate the result

CREATE TABLE yourtable 
  ( 
     id   INT IDENTITY(0, 1), 
     result_column AS RIGHT(Year(Getdate()), 2) + ' 21 ' 
        + RIGHT('000'+Cast(id AS VARCHAR(20)), 4) persisted, 
     NAME VARCHAR(50) 
  ) 

INSERT INTO yourtable 
            (NAME) 
VALUES      ('prdp'), 
            ('prdp1') 

SELECT * 
FROM   yourtable 

Result:

id  result_column   name
--  -------------   -----
0   16 21 0000      prdp
1   16 21 0001      prdp1

Note: Even after ID = 9999 the result_column will generate only last 4 characters from ID