Hi All,
I have a table column which is my primary key. The column value starts at 10000 . when a data is inserted to the table the column value will be appended with 'POD' ie it will be like POD10000,POD10001,POD10002 etc...It was working fine .But due to some modifications
in a certain page I had to insert an extension '-1','-2' .Now whenever I insert a new value I get an error "
Conversion failed when converting the varchar value '10001-1' to data type int
".
So my question how can I find the next highest value excluding the extension '-'.
Suppose if the values in coulmn is
POD10000
POD10000-1
POD10000-2
POD10000-3
POD10004
POD10005
POD10005-1
I need to get the value as 10006.Please help me.
What I have tried:
select isnull(max(CONVERT(INT,substring(col1,4,11)))+1,0) as col1 from tab_1
Here I will get the next value as 10006 if there are no values with '-'.How to get the next highest value if there is '-'.
Any help will be really appreciated.Thanks in advance.