vecerda (10/1/2014)
Hello,I need to auto increment an alphanumeric field in a table.
The process is as follows:
1. First position is static letter C for contact or static letter A for account - field contact_id
2. The following 6 positions are numeric - example of the string would be C004658
3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659
Thanks in advance for all the help
My advice in a nutshell is don't do this. There are three major reasons this is a horrible design choice. First, you are now combining two pieces of information in a single column (RowType and RowID). This is in direct violation of 1NF.
Second, unless you use a sequence you are going to have major challenges with concurrency.
Thirst, this is a serious PITA to deal with.
If you are stuck with it then I would deal with this as a computed column like Luis already suggested. That would also eliminate the need for a procedure to run every 5 minutes. You don't need the procedure at all. Something along these lines should work.
create table Something
(
SomeID int identity not null,
AccountType char(1),
MyIncrementingAlphaColumn as AccountType + RIGHT(REPLICATE('0', 6) + CAST(SomeID as varchar(6)), 6)
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/