September 19, 2011 at 9:22 am
I have an Identity column in my table and would like to append a letter to the front. What is the best approach to accomplish this? Is this something I should let the front-end application handle? Any help will be greatly appreciated.
September 19, 2011 at 9:25 am
Will the letter be auto-generated and incremented? Will it be a meaningful value? (Half the point of an ID column is that it has no business meaning.)
Either way, your best bet is probably to keep the letter in a separate column and use a computed column to concatenate the values together.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 19, 2011 at 9:37 am
GSquared (9/19/2011)
Will the letter be auto-generated and incremented? Will it be a meaningful value? (Half the point of an ID column is that it has no business meaning.)Either way, your best bet is probably to keep the letter in a separate column and use a computed column to concatenate the values together.
I full on agree here.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 19, 2011 at 9:38 am
You can't add a letter in the column. You could create a computed column that includes the identity as a component (http://voiceofthedba.wordpress.com/2011/08/30/computed-columns-and-udfs/), but it could help if you explained if you are looking for some pattern or give more information on what you are looking for (as Gus asked)
September 19, 2011 at 11:21 am
Thanks guys. I simply want to add the letter "R" to the indentity value.
R0001
R0002
R0003
etc...
September 19, 2011 at 11:28 am
Try adding a column like this:
alter table dbo.MyTable
add RID as 'R' + right('00000' + cast(ID as varchar(5)), 5);
You can probably index a column defined that way, if you want to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 19, 2011 at 11:43 am
That's perfect! Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply