Post reply

Retrieving max value from a varchar field

  • March 29, 2004 at 9:14 pm

    #107470

    Hello,

    I have ID field in a table which assigns unique ID to each new record inserted in the table. IDs have a form M###### where M is a character and ###### is a number which increments every time new record is added.

    for instance, this table would have records having IDs M000001, M000002, ..... M000010 ... etc..

    Any ideas how can I assign such ID automatically, or is there a way to get record last inserted.

    Thanks

  • March 30, 2004 at 5:37 am

    #501044

    Personally I would use an identity field to do the incremementing. The value can bet cast to a varchar and preceeding 0's and M can be concatinated on for looks. Is there a reason specific why that would not work for you?

  • March 30, 2004 at 6:52 am

    #501062

    I am not an experienced SQL Server programmer ... not sure how this can be done

    I would appreciate if someone can give me ideas as to how this can be achieved.

  • March 30, 2004 at 2:58 pm

    #501190

    Your best bet is to use a seperate identity column to increment.

    You can alter your table as follows:

    alter table

     my_table

    add

     my_idcol numeric identity(1,1)

    go

    If you apply this to the table it will add the column and back fill it with values from the start of the table. You can change the first digit after IDENTITY to allow the increments to start from a number other than 1. Changing the second number will allow you to change the increment steps - 1,2,3,... and so on.

    As Antares said, you can concatenate on the 'M' and the zeros at any point after that - when you retrieve the values from the table - unless you have a specific reason to store it in this format.

    Thanx, I'm here all Week,

    SmallYellow

  • March 30, 2004 at 3:08 pm

    #501195

    Thanks guys for your help,

    Here is my solution to the problem. Instead of creating another column just for storing ids I am using count() function in select statement to get number of records in the table ..

    Rest is simple I have to just concatenate this value with M and 0s

  • March 31, 2004 at 7:32 am

    #501274

    If you delete a record from the table, unless it's the last one, won't a count give you a duplicate?

    ie:  Records 1 to 10, #7 deleted, so now you have 9 records, meaning count + 1 will be 10, which you already have.

    Mattie

  • March 31, 2004 at 4:42 pm

    #501388

    Oooo woww ..... I didnot think about that, that sure would create duplicate entries. Anyway good thing is right now the system is designed is such a way that no entry in the table can be deleted so that problem wont arise.

    But at a later stage I do plan to move some of the records from this table to a back up table so that queries can run faster. Thanks for bringing this to my notice.

  • April 4, 2004 at 10:38 am

    #501703

    The count(*) will always give unique id's but the row data will have a dynamic id and not a static id. For a given row the id will be changing depending on the sequence in which sqlserver retrieves data.

    If you have the scope to add a column, continuing on SmallYellow's idea add the identity column and convert the existing "id" column to a computed column as

    alter table my_table alter column id as 'M'+right('0000000000'+cast(my_idcol as varchar(20)),10)

    Hope this helps.

    AM

  • April 4, 2004 at 1:04 pm

    #501705

    Thanks mathurar .... 'myidcol' was exactly what I was looking for .... since I didnt find tat on time I decided to look for other means of getting an unique identifier

    Thanks again for the help

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply