• 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/