0

I have a table in SQL Server

create table student
(
    id int,
    batch varchar(10),
    branch varchar(10),
    registerNumber varchar(30)
)

I want my registerNumber to be of type batch+branch+id
eg a row in student id:1, batch:17, branch:BIT then the registerNumber should be 17BIT1

I tried to do this by using a default value in create table but it does not allow me to reference the columns of the same table

edit

thank you for the answers I went with this 
create table stud101
(
batch int,
branch char(3),
srNum int identity(1,1),
registerNum as Concat(cast(batch as char(2)),branch,cast(format(srNum,'0000') as char(4))
))

insert into stud101 values(17,'BIT')


select * from stud101
pranav fokmare
  • 3
  • 2
  • You will likely regret your accepted answer once you realize what happens when your ID values reach 10, 100, 1000, etc. Similar issues will become apparent when you start randomly using longer values for batch and branch. Usually you need much stricter formatting rules for something like this - and it is questionable if you need this at all. – SMor Mar 04 '21 at 13:09
  • oh yeah this is a simplified version of the table, I then added format and convert to the data types so that it becomes like 17BIT0001, 17BIT0088 and so on my final table looks like in the edit – pranav fokmare Mar 05 '21 at 08:46

1 Answers1

0

Just use a computed column:

create table student
(
    id int,
    batch varchar(10),
    branch varchar(10),
    registerNumber as batch + branch + id
)
Dale K
  • 25,246
  • 15
  • 42
  • 71