Conversion failed when converting the nvarchar value '/' to data type int.
-
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, GeneralAssemblyFROM dbo.MembersWHERE (Member = 1) AND (SpecialCode IS NOT NULL)ORDER BY expr1, EXPR2Field 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 .
-
Hi
Is it possible you have some values formatted differently? Such as '006403/' ? See the example code below, the first value is fine but the second one will throw your error
with cte as
(
select val from (values ('006403/1'), ('006403/')) as tvc(val)
)
select val ,
CAST(CAST(LEFT(val, CHARINDEX('/', val) - 1) AS nvarchar) AS int) AS Expr1,
CAST(CAST(RIGHT(val, CHARINDEX('/', val) - 6) AS nvarchar) AS int) AS Expr2
from cte;This adjusted code (using substring instead of right) may be closer to what you need (the cast to int will generate the value 0 if there is nothing after the slash) :
with cte as
(
select val from (values ('006403/1'), ('006403/')) as tvc(val)
)
select val ,
CAST(CAST(LEFT(val, CHARINDEX('/', val) - 1) AS nvarchar) AS int) AS Expr1,
CAST(CAST(substring(val, CHARINDEX('/', val)+1 , len(val) - CHARINDEX('/', val)) AS nvarchar) AS int) AS Expr2
from cte; -
thank you for reply
right side OK working on EXPR2 but
expr1 not working as left side
my query usedSELECT TOP (99.9999999) PERCENT SpecialCode , CAST(CAST(LEFT(SpecialCode, CHARINDEX('/', SpecialCode) - 1) AS nvarchar) AS int) AS expr1,CAST(CAST(substring(SpecialCode, CHARINDEX('/', SpecialCode)+1 , len(SpecialCode) - CHARINDEX('/', SpecialCode)) 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
it give me error
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
how to solve that error - ahmed_elbarbary.2010 - Monday, June 4, 2018 10:16 PM
I think your expr1 is returning negative(-) values .Kindly provide sample data to confirm the same.
Saravanan
- ahmed_elbarbary.2010 - Monday, June 4, 2018 7:54 PM
Let's try the following:
SELECT TOP (99.9999999) PERCENT
CASE
WHEN CHARINDEX('/', SpecialCode) < 2 THEN CONVERT(int, NULL)
ELSE TRY_CONVERT(int, LEFT(SpecialCode, CHARINDEX('/', SpecialCode) - 1)
END AS Expr1,
--CAST(CAST(LEFT(SpecialCode, CHARINDEX('/', SpecialCode) - 1) AS nvarchar) AS int) AS Expr1,
CASE
WHEN (CHARINDEX('/', SpecialCode) = 0) OR (LEN(SpecialCode) = CHARINDEX('/', SpecialCode))
THEN CONVERT(int, NULL)
ELSE TRY_CONVERT(int, RIGHT(SpecialCode, LEN(SpecialCode) - CHARINDEX('/', SpecialCode)))
END AS Expr2,
--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;Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy) -
This was removed by the editor as SPAM
-
Here's an example with some possible scenarios:
SELECT SpecialCode,
CAST(SUBSTRING(SpecialCode, 0, ISNULL( NULLIF(CHARINDEX('/', SpecialCode), 0), 10)) AS int) AS Expr1,
CAST(SUBSTRING(SpecialCode, ISNULL( NULLIF(CHARINDEX('/', SpecialCode), 0), 10) +1, 10) AS int) AS Expr2
FROM (VALUES('006403/1'),
('006403'),
('006403/'),
('/'),
('/1'),
(''),
(NULL))x(SpecialCode)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply