how to done auto increment with varchar datatype...

[ripa]

Session_id varchar(5)prima ry key
Name varchar(30)
Price int
in this mysql database I want to increment the session Id as
S0001 so how can I do with auto increment....
pls suggest me,,,

pls quick reply...

[michaeldebruin]

You should be able to set auto increment on the field within the database when you are creating the fields. And then when putting data in the database the auto increment should do his work automatically.

[ariful alam]

create a procedure that has a variable name @last_id

put the last session_id in this variable @last_id

if exists(select * from <table_name>)
begin
     select top 1 @last_id=session_id from <table_name> order by session_id desc
     --use substring() method to cut out the 'S' and add 1 to 
     --the existing value in another variable. this will cut 
     --out pre zero's. so you have to test length to add pre 
     --zero's if needed. and then use the last created id to 
     --insert a new record.
end
else
begin
     set @last_id = 'S0001'
     insert <table_name> values (@last_id[,<other_field>])
end

hope, helps. :)