如何从具有char' - '的表中的主键列获取下一个最大值 [英] How to get the next maximum value from a primary key column in a table that has a char '-'
问题描述
大家好,
我有一个表列,这是我的主键。列值从10000开始。当数据插入表格时,列值将附加'POD',即它将像POD10000,POD10001,POD10002等...它工作正常。但是由于一些修改
在某个页面中,我必须插入一个扩展名'-1',' - 2'。现在每当我插入一个新值时,我都会收到错误
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
。
所以我的问题是如何找到下一个最高值,不包括扩展名' - '。
假设coulmn值是否为
POD10000
POD10000-1
POD10000-2
POD10000-3
POD10004
POD10005
POD10005-1
我需要得到10006的价值。请帮助我。
我尝试了什么:
".
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
这里我会说如果没有带' - '的值,则下一个值为10006。如果有' - ',如何获得下一个最高值。
任何帮助都会非常感激。谢谢提前。
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.
推荐答案
'10000-1'无法转换为INT(显然)......
如果你想保持它们的顺序(10000 ,10000-1,10000-2,10001,10002)你有两个选择:
1.使用浮点并将' - '替换为'。'... - 它给10000,10000.1 ,10000.2,10001,10002
2.将' - '替换为'。',将倍数替换为10(如果在'-'...后面有多于一位数,则替换为100) - 它给出100000,100001,100002,100010,100020
'10000-1' can not be converted to INT (obviously)...
If you want keep them in order (10000, 10000-1, 10000-2, 10001, 10002) you have two options:
1. Use floating point and replace the '-' to '.'... - it gives 10000, 10000.1, 10000.2, 10001, 10002
2. Replace the '-' with '.' and multiple by 10 (or 100 if you have more then one digit after the '-'...) - it gives 100000, 100001, 100002, 100010, 100020
这篇关于如何从具有char' - '的表中的主键列获取下一个最大值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!