Conversion failed when converting the nvarchar value '/' to

Question

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 .

Bryian Tan · Answer

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. The quick work around is to replace this line CAST ( CAST ( RIGHT (SpecialCode, CHARINDEX( '/' , SpecialCode) - 6) AS NVARCHAR) AS INT ) AS Expr2 with CAST ( REPLACE ( CAST ( RIGHT (@SpecialCode, CHARINDEX( '/' , @SpecialCode) - 6) AS NVARCHAR), '/' , '' ) AS INT ) AS Expr2

ahmed elbarbary · Answer

can you help me to do that without using cast i need to do that by using substring

Nitin Sontakke · Answer

For investigation purpose, please be prepared to fire few more queries. Just select that column and see if data is consistent. There is no need to jump to conversion while investigating. You are using hard coded 6 value in your query is that correct? Is / coming at exactly same location in all rows? Check if / is getting included in column you are trying to convert to integer. You will need to investigate the data. Keep your original query aside and investigate data. If you find any issues, resolve them and then go back to your original query. Modify that too, if necessary. Hope you get the point!