将nvarchar值'/'转换为数据类型int时转换失败。 [英] Conversion failed when converting the nvarchar value '/' to data type int.
问题描述
我有以下SQL查询:
SELECT < span class =code-keyword> TOP ( 99 。 9999999 ) PERCENT CAST(CAST( LEFT (SpecialCode,CHARINDEX('' /',SpecialCode) - 1 ) AS NVARCHAR ) AS INT )< span class =code-keyword> AS Expr1
,CAST(CAST( RIGHT (SpecialCode,CHARINDEX(' /',SpecialCode) - 6 ) AS NVARCHAR ) AS INT ) AS Expr2
,MemberCode,SpecialCode,StartingDate,JoinDate
,EndDate,BirthDate,NAME, FirstName
,MiddleName,LastName,国籍,国籍2
,教育,地址,城市,国家,PostalCode
,Homephone1,Homephone2,Workaddress,WorkPhone1
,Ext1,WorkPhone2,Ext2, Mobile1,Mobile2
,FaxNumber,Email,EmergencyTel,NoOfYearsTennis
,ClubName,NationalRank,Year,class,NoOfYearsTournament
,Note,MSRelation,RelatedMemberCode,MScategory
,Member,ParentCard1, ParentCard2,ExpDate,position
,worktype,NonActive,MSKind,MSType,CardNumber
,CardReceived,Discount,Religious,Gender,Organization
,RelatedMemberCodeOld,CreateUserId,CreateDateAndTime,UserId
, DateAndTime,HasNotEmail,VillaOwner,Homephone3,Mobile3
,Email2,Email3,BirthPlace,NationalNo,EndDateNationalNo
,MiritialStatusID,WifeNo,GeneralAssembly
FROM dbo.Members
WHERE (Member = 1 )
AND (SpecialCode IS 不 NULL )
ORDER BY expr1
,EXPR2
字段SpecialCode在会员表中找到并且具有类似的值
006403 / 1当我运行上面的查询时它给我错误
消息245,等级16,状态1,行1
转换时转换失败nvarchar值'/'到数据类型int。
为什么以及如何解决这个错误。
我尝试过:
将nvarchar值'/'转换为数据类型int时转换失败。
我猜,数据问题?可能的一个SpecialCode值不是###### /#格式?
这里我是如何复制错误的。
< pre lang =SQL> DECLARE @ SpecialCode VARCHAR ( 50 )
SET @ SpecialCode = ' 012345 /'
SELECT CAST(CAST( RIGHT ( @ SpecialCode ,CHARINDEX(' /', @ SpecialCode ) - 6 ) AS NVARCHAR ) AS INT )
SEL ECT CAST(CAST( LEFT ( @ SpecialCode ,CHARINDEX(' /', @ SpecialCode ) - 1 ) AS NVARCHAR ) AS INT )
结果:
Msg 245,等级16,状态1,行3
将nvarchar值'/'转换为数据类型int时转换失败。
不清楚是什么你的目标是,但如果是这样的话,快速的解决方法是用空字符串替换/
DECLARE @ SpecialCode VARCHAR ( 50 )
SET @ SpecialCode = ' 012345 /'
SELECT CAST(CAST( LEFT ( @ SpecialCode ,CHARINDEX(' /', @ SpecialCode ) - 1 ) AS NVARCHAR ) AS INT ),
CAST(
REPLACE(CAST( RIGHT ( @ SpecialCode ,CHARINDEX(' /', @ SpecialCode ) - 6 )< span class =code-keyword> AS NVARCHAR ),'/',' ') AS INT )
结果:12345 0
I have SQL query as following :
SELECT TOP (99.9999999) PERCENT CAST(CAST(LEFT(SpecialCode, CHARINDEX('/', SpecialCode) - 1) AS NVARCHAR) AS INT) AS Expr1
,CAST(CAST(RIGHT(SpecialCode, CHARINDEX('/', SpecialCode) - 6) AS NVARCHAR) AS INT) AS Expr2
,MemberCode ,SpecialCode ,StartingDate ,JoinDate
,EndDate ,BirthDate ,NAME ,FirstName
,MiddleName ,LastName ,Nationality ,Nationality2
,Education ,Address ,City ,Country ,PostalCode
,Homephone1 ,Homephone2 ,Workaddress ,WorkPhone1
,Ext1 ,WorkPhone2 ,Ext2 ,Mobile1 ,Mobile2
,FaxNumber ,Email ,EmergencyTel ,NoOfYearsTennis
,ClubName ,NationalRank ,Year ,class ,NoOfYearsTournament
,Note ,MSRelation ,RelatedMemberCode ,MScategory
,Member ,ParentCard1 ,ParentCard2 ,ExpDate ,position
,worktype ,NonActive ,MSKind ,MSType ,CardNumber
,CardReceived ,Discount ,Religious ,Gender ,Organization
,RelatedMemberCodeOld ,CreateUserId ,CreateDateAndTime ,UserId
,DateAndTime ,HasNotEmail ,VillaOwner ,Homephone3 ,Mobile3
,Email2 ,Email3 ,BirthPlace ,NationalNo ,EndDateNationalNo
,MiritialStatusID ,WifeNo ,GeneralAssembly
FROM dbo.Members
WHERE (Member = 1)
AND (SpecialCode IS NOT NULL)
ORDER BY expr1
,EXPR2
Field SpecialCode found in Members Table and have values like that
006403/1 when i run query above it give me error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '/' to data type int.
why and how to solve this error .
What I have tried:
Conversion failed when converting the nvarchar value '/' to data type int.
I'm guessing, data issue? Possible one of the SpecialCode value not in ######/# format?
Here how I replicate the error.
DECLARE @SpecialCode VARCHAR(50) SET @SpecialCode = '012345/' SELECT CAST(CAST(RIGHT(@SpecialCode, CHARINDEX('/', @SpecialCode) - 6) AS NVARCHAR) AS INT) SELECT CAST(CAST(LEFT(@SpecialCode, CHARINDEX('/', @SpecialCode) - 1) AS NVARCHAR) AS INT)
Result:
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the nvarchar value '/' to data type int.
Not clear what your objective are, but if that the case, the quick work around is to replace the / with empty string
DECLARE @SpecialCode VARCHAR(50) SET @SpecialCode = '012345/' SELECT CAST(CAST(LEFT(@SpecialCode, CHARINDEX('/', @SpecialCode) - 1) AS NVARCHAR) AS INT), CAST( REPLACE(CAST(RIGHT(@SpecialCode, CHARINDEX('/', @SpecialCode) - 6) AS NVARCHAR),'/','') AS INT)
Result: 12345 0
这篇关于将nvarchar值'/'转换为数据类型int时转换失败。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!