Conversion failed when converting the nvarchar value '/' to data type int.

Question

Conversion failed when converting the nvarchar value '/' to data type int.

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
June 4, 2018 at 7:54 pm

#354118

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, 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 .
June 21, 2018 at 1:07 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 1

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;

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

thank you for reply
right side OK working on EXPR2 but
expr1 not working as left side
my query used
SELECT 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

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 3

ahmed_elbarbary.2010 - Monday, June 4, 2018 10:16 PM
thank you for reply
right side OK working on EXPR2 but
expr1 not working as left side
my query used
SELECT 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

I think your expr1 is returning negative(-) values .Kindly provide sample data to confirm the same.

Saravanan

sgmunson SSC Guru Points: 110639 More actions · Answer 4

ahmed_elbarbary.2010 - Monday, June 4, 2018 7:54 PM
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, 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 .

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)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

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)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2