Click here to Skip to main content
15,912,400 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Sir ,
I want to start the ID is M001 and is auto increment with M009 how it is possible in sqlserver;
Plz give me solution .
Thanks sir

arvind kumar singh
Posted

Never combine separate data in a single field.

For this case, use two different fields, identity and the prefix field. If you want to have a field that shows the data as you presented, you can create a view or use Computed columns, see: http://msdn.microsoft.com/en-us/library/ms191250.aspx[^].

However if the M is a constant, I wouldn't put it into the database at all. At max I would include it to the computed column, but I see no reason for that either.
 
Share this answer
 
Comments
Arvind_singh 9-Apr-11 5:54am    
Thanks Sir
Wendelius 9-Apr-11 5:58am    
You're welcome :)
It isn't possible.

SQL Server Identity fields can only be numeric. If you want this, then the only solution is to discard the "M" part and add that on later whenever you use the field. MSDN[^]

"sir I know this is not possible through Identity.But how can we do this please give me solution ;
Thanks Sir"


The only way to autoincrement numbers in SQL Server is to use identity. The only solution is to not store the True ID in the table if you need an automatically changing identity field.

If you need to have IDs which are "Mnnn" where "nnn" starts with 1 and increments by 9 each time, then I would suggest use an identity field in the table with an increment of one, and have a pair of translation methods:
private static string ToExternalID(int id)
    {
    return string.Format("M{0:d3}", id * 9);
    }
private static int FromExternalID(string id)
    {
    return int.Parse(id.Substring(1)) / 9;
    }
You will probably want to add some error checking to these before you use them for real!
 
Share this answer
 
v2
Comments
Arvind_singh 9-Apr-11 5:47am    
sir I know this is not possible through Identity.But how can we do this please give me solution ;
Thanks Sir
OriginalGriff 9-Apr-11 6:03am    
Answer updated

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900