CODEWITHSUNDEEP
sql-serverdatabase
Jibin Balachandran
Jibin Balachandran

Reputation: 3441

Concat a string and an int value to a DB column value

I have a table, in which I have a column to store user name. I was stroring it as varchar of format user1,user2,user3(its not inserted by user,automatically it should get generated) So for this I need to get the max int value of users and then increment it by 1 and append it with string user and store it to the next row.

Upvotes: 0

Views: 102

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

If you insist on saving the user prefix to the number, you can add an identity column, and create that user column as a computed column:

CREATE TABLE tblUsers
(
    UserId int Identity(1,1) PRIMARY KEY,
    UserName As 'User'+ CAST(UserId as varchar),
    -- Other columns
)

This way whenever you select the UserName column it's value will be calculated.
You can also specify it as PERSISTED, meaning it's value will be saved in the database and only change if the value of one of it's components will change.

The big benefit here is that the database will handle all the hard work for you, plus uniqueness is guaranteed since it's based on the value of the primary key of the table.

Upvotes: 1

Leet-Falcon
Leet-Falcon

Reputation: 2147

Add an auto-incremented field,

CREATE TABLE Users
(
ID int NOT NULL AUTO_INCREMENT=1,
LastName varchar(255) ... ,
FirstName varchar(255) ... ,
... ,
SeqToken varchar(255)
)

Than create a trigger on insert:
The trigger will auto-generate the SeqToken field:

CREATE TRIGGER trig_insert_user
ON [Users]
AFTER INSERT
AS
   UPDATE [Users] 
   SET SeqToken='User'+CAST(ID AS VARCHAR) 
   WHERE ID=INSERTED.ID

Upvotes: 1

Related Questions