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

Question

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.

Name your Column User and only store integer values in your column , 1, 2,3 .... etc — M.Ali
– M.Ali, Commented Nov 17, 2015 at 9:01
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 and then increment it by 1 and append it with user. — Jibin Balachandran
– Jibin Balachandran, Commented Nov 17, 2015 at 9:04
post in your question and ask clearly what you want and what you have tried so far.. — Pathik Vejani
– Pathik Vejani, Commented Nov 17, 2015 at 9:05
@M.Ali I can't do that, this value works like a auto generated token number — Jibin Balachandran
– Jibin Balachandran, Commented Nov 17, 2015 at 9:06

Zohar Peled · Accepted Answer · 2015-11-17 09:54:39Z

1

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.

answered Nov 17, 2015 at 9:54

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Leet-Falcon · Accepted Answer · 2015-11-17 09:40:32Z

1

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

answered Nov 17, 2015 at 9:40

Leet-Falcon

2,1472 gold badges17 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related