identity value with some string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

identity value with some string

Author

Topic

venkatch786
Starting Member

8 Posts

Posted - 2010-06-29 : 08:17:53

Hi,
I am using identity for empid column in a table.
But I need to display empid with "Emp" string for every row

empid
------
Emp101
Eemp102
Eemp103

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 08:21:35

If its for only display issue then tell the front end guys to show Emp with empid column
Or concatenate emp with empid in your SP's

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

venkatch786
Starting Member

8 Posts

Posted - 2010-06-29 : 08:28:39

Thanks for your reply,

I want to store data in table like this way Emp101 ....
not in front end display

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-29 : 08:32:51

When you select data, use

select columns, 'emp'+right('00000000'+cast(empid as varchar(10)),8) from your_table

Madhivanan

Failing to plan is Planning to fail

Devart
Posting Yak Master

102 Posts

Posted - 2010-06-29 : 08:44:29

Hello,
You can use a trigger for this operation.
For example:

create trigger <trigger_name> on <table_name>
for insert [,update]
as
set nocount on
update <table_name>
set <field_name>='emp'+right('00000000'+cast(_ins.empid as varchar(10)),8)
from inserted _ins
inner join <table_name>
on _ins.empid=<table_name>.empid

P.S. <field_name> can be null.

Best regards,

Devart Team

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-29 : 08:48:58

quote:
Originally posted by Devart

Hello,
You can use a trigger for this operation.
For example:

create trigger <trigger_name> on <table_name>
for insert [,update]
as
set nocount on
update <table_name>
set <field_name>='emp'+right('00000000'+cast(_ins.empid as varchar(10)),8)
from inserted _ins
inner join <table_name>
on _ins.empid=<table_name>.empid

P.S. <field_name> can be null.

Best regards,

Devart Team

Trigger is not the solution. Better approach is to have a computed column

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 09:06:23

Hi madhi,

Can you please demonstrate computed columns or do you have such a link for the same.

Thanks

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Devart
Posting Yak Master

102 Posts

Posted - 2010-06-29 : 09:10:07

Hello,

quote:
Trigger is not the solution. Better approach is to have a computed column

This is true only for INSERT operation. What should one do in case of UPDATE operation? We do not know where "EMP" prefix comes from.

Best Regards,

Devart Team

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 09:14:10

I found something that may help you...

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Devart
Posting Yak Master

102 Posts

Posted - 2010-06-29 : 09:14:48

Hello,

quote:
Can you please demonstrate computed columns or do you have such a link for the same.

For example:

create table <table_name> as
...
<field_name> AS ('emp'+right('00000000'+cast(empid as varchar(10)),8))
...

Best regards,

Devart Team

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 09:15:58

quote:
Originally posted by Devart

Hello,

quote:
Can you please demonstrate computed columns or do you have such a link for the same.

For example:

create table <table_name> as
...
<field_name> AS ('emp'+right('00000000'+cast(empid as varchar(10)),8))
...

Best regards,

Devart Team

Thanks for the reply..

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

venkatch786
Starting Member

8 Posts

Posted - 2010-06-29 : 09:22:11

Thanks for all replies...

Venkat..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-29 : 10:02:50

quote:
Originally posted by Devart

Hello,

quote:
Trigger is not the solution. Better approach is to have a computed column

This is true only for INSERT operation. What should one do in case of UPDATE operation? We do not know where "EMP" prefix comes from.

Best Regards,

Devart Team

Identity column cannot be updated

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources