Conversion failed when converting the nvarchar value '/' to

ahmed elbarbary

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 .

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bryian Tan8y ago
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 elbarbary8y ago
can you help me to do that without using cast
i need to do that by using substring
Nitin Sontakke8y ago
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!