将varchar值'A0001'转换为数据类型int时转换失败

When using this script:

使用此脚本时:

DECLARE @FORDERBZV2     varchar(20)
DECLARE @FORDERBZV          varchar(20)

SELECT  @FORDERBZV      = (SELECT TOP 1 [FOR] FROM [Mil].[dbo].[FXO] where FOR like 'T0%' order by FOR desc)

--[FOR] is declared as varchar(20)  
SELECT  @FORDERBZV2 = right (@FORDERBZV, 19) + 1

--and after i run
insert into [Mil].[dbo].[FEO]
Values (@FORDERBZV2,....)

I get this error:

我收到此错误:

Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value 'T000005571' to data type int.

消息245,级别16,状态1,行6转换将varchar值'T000005571'转换为数据类型int时失败。

I don't understand why, because all fileds are declared as VARCHAR(20):

我不明白为什么,因为所有文件都声明为VARCHAR(20):

FOR from [Mil].[dbo].[FXO] = VARCHAR(20)
@FORDERBZV  =  VARCHAR(20)
@FORDERBZV  =  VARCHAR(20)

Also, the script will run correctly once, but when I try to run it a second time, I get the error.

此外,该脚本将正确运行一次,但当我尝试再次运行它时,我收到错误。

1 个解决方案

#1

So, the problem lies in this code line: SELECT @FORDERBZV2 = right (@FORDERBZV, 19) + 1.
The reason you get the error is that your string is not actually 20 chars long, but it's shorter, so the right function simply returns the entire string.

所以,问题在于这个代码行:SELECT @ FORDERBZV2 = right(@FORDERBZV,19)+ 1.你得到错误的原因是你的字符串实际上不是20个字符长,但它更短,所以正确的功能只是返回整个字符串。

I'm assuming you want to get the letter T following by zero-padded consecutive numbers, so you should probably do something like this:

我假设你想要用零填充的连续数字得到字母T,所以你应该做这样的事情:

DECLARE @FORDERBZV2     varchar(20)
DECLARE @FORDERBZV          varchar(20)

SELECT  @FORDERBZV      = (SELECT TOP 1 [FOR] 
                           FROM [Mil].[dbo].[FXO] 
                           WHERE [FOR] 
                           LIKE 'T0%' 
                           ORDER BY [FOR] DESC)

SELECT  @FORDERBZV2 = 'T' + RIGHT(
                            REPLICATE('0', LEN(@FORDERBZV)-1) + 
                            CAST (
                            CAST(RIGHT(@FORDERBZV, LEN(@FORDERBZV)-1) AS int) + 1 
                            AS varchar(20))
                            , LEN(@FORDERBZV)-1)

insert into [Mil].[dbo].[FEO]
Values (@FORDERBZV2,....)

see fiddle here.

看这里的小提琴。

#1