Error on query below conversion faild when converting 5/1 to int

Question

0.00/5 (No votes)

See more:

problem

error on query below conversion fails when converting 5/1 to int

<pre lang="CSS">DataSample

SpecialCode   expr1   EXPR2
000151/1      000151    1
000151/2      000151    2
000151/3      000151    3
006495/1      006495    1
006495/2      006495    2

data above not have any problem but i make it to show what query doing

but error above may be occurred on data on expr1,and EXPR2 when conversion to integer

So that

1- what i modify on query to avoid this error display because more use write special code wrong and save it wrong ?

2-why this error happen and cases that do this error ?

What I have tried:

Query sql server 2014 as below :

SELECT  FirstPrintCardFooter.FooterNotes,
FirstPrintCardFooter.PrintFlag,   dbo.VMainMembers.TransactionNo,
dbo.VMainMembers.PaymentDate,   
dbo.Members.MemberCode,MSRelation.MSRelationName,MSRelation.MSRelationId, 
dbo.Members.SpecialCode, dbo.Members.Name,dbo.Members.RelatedMemberCode,MemberImage,VMainMembers.totalcredit,
CAST(CAST(LEFT(dbo.Members.SpecialCode, CHARINDEX('/', dbo.Members.SpecialCode + '/') - 1) AS nvarchar) AS int) AS expr1 ,
CAST(CAST(substring(dbo.Members.SpecialCode, CHARINDEX('/', dbo.Members.SpecialCode)+1 , len(dbo.Members.SpecialCode) - CHARINDEX('/', dbo.Members.SpecialCode)) AS nvarchar) AS int) AS EXPR2 
        
FROM            dbo.Members left join MSRelation on Members.MSRelation=MSRelation.MSRelationId 
LEFT JOIN       dbo.VMainMembers 
ON SUBSTRING(dbo.Members.SpecialCode, 0, CHARINDEX('/', dbo.Members.SpecialCode, 0))=SUBSTRING(dbo.VMainMembers.SpecialCode, 0, CHARINDEX('/', dbo.VMainMembers.SpecialCode, 0)) 						
LEFT JOIN FirstPrintCardFooter 
ON MEMBERS.MemberCode = FirstPrintCardFooter.MemberCode
AND  dbo.VMainMembers.[Year] = FirstPrintCardFooter.TrxYear
WHERE FirstPrintCardFooter.MemberCode IS  NULL    
and dbo.VMainMembers.TransactionNo is not null  and VMainMembers.Year=2018 order by expr1 ,EXPR2

Posted 12-Feb-19 14:37pm

ahmed_sa

Updated 12-Feb-19 21:22pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-02-12T21:22:00

If I extract your code to check, and use your sample data:

SQL

DECLARE @SpecialCode NVARCHAR(MAX);
SET @SpecialCode = '000151/1'
SELECT 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,
SUBSTRING(@SpecialCode, 0, CHARINDEX('/', @SpecialCode, 0)),
SUBSTRING(@SpecialCode, 0, CHARINDEX('/', @SpecialCode, 0))

Then it works: I get nop errors:

expr1   EXPR2   (No column name)   (No column name)
151     1       000151             000151

So at a guess, there is data in your table that does not match the pattern of the data you provided.

Go back to your DB and check the content of your Members.SpecialCode very carefully - I suspect there is bad data in there!