how to generate proprietary key values 1

[brianpercival]

hi all,

here is my situation. User types xx in a field, yy in another field, and I have to create a new record in the table with a field having unique value xx-yy-nnnn.

nnnn is a number, 1 more than the last record with xx-yy-(nnnn-1)

I hope I didn't make it clumsy.

basically if there is a record with field as N1-BG-0116, then when I am adding a new record with N1-BG-, I want the field value for this to be N1-BG-0117

any way to do that declaratively?


regards,
Brian

 

[jbenson001]

Try this.. I have not tested it. If you know that your column will always have 4 characters as the last set of characters then this might help.

Select Convert(Int, Max(Right(<col>, 4 ) + 1 )