Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
proone23
Starting Member
10 Posts |
 Posted - 2011-05-03 : 14:49:53
|
| I have a stored procedure to insert data, I am stuck on one part:I have a varchar field that contains 3 letters (always the same) followed by 6 numbers that increase by one each time a line is inserted.'ppp123456'I would like to find the highest value and add 1 to it.Any idea how I accomplish this?Thank you in advance! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-03 : 15:08:54
|
| Something like thisdeclare @str varchar(9)SET @str = 'ppp123456'SELECT max(convert(int,right(@str,6)))+1JimEveryday I learn something that somebody else already knew |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
proone23
Starting Member
10 Posts |
Posted - 2011-05-03 : 15:31:31
|
| Thank you every one! Table cannot be modified/changed. Tkizer, thanks for the heads up - was not aware of identity until now :)For learning purposes I want to work through what Jim the genius suggested. It worked!!!DECLARE @ufo1 varchar(9), @ufo varchar(9) set @ufo1 = (select max(ufo_num) from table) set @ufo = (SELECT convert(int,right(@ufo1,5))+1)Can the above be combined/shortened? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-03 : 15:39:04
|
| Yes. Just replace @str with ufo_num in the query I gave, and the add you from clause, of course.JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
Help with programming - ?trim some letters & +1?