Avatar of Software Engineer
Software Engineer
 asked on

T-SQL: Conversion failed when converting the varchar value '12646–112521' to data type int.

Hi:

Below is a snippet from my T-SQL code.  It is giving me the following error:

Conversion failed when converting the varchar value '12646–112521' to data type int.

Please let me know how to add syntax to fix this.

Thanks!

Software Engineer

CASE WHEN ORDERLN.ITEMID IS NOT NULL THEN 
Ltrim(rtrim(ORDERLN.ITEMID)) + '–' + ltrim(rtrim(ORDERLN.LINEID))
ELSE '1' + '–' + ltrim(rtrim(ORDERLN.LINEID)) END as ITEMID,

Open in new window

SoftwareProgrammingSQL* T-SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Software Engineer

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jim Horn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Pavel Celba

It seems you are comparing the value created in the above CASE statement which is a text string containing non-numeric character to integer value.

In such case the conversion must fail.
arnold

Are you looking for the resulting computation of one number minus the other?

What is the end result that you are looking to have when successful?
Software Engineer

ASKER
Hi Jim Horn:

"Perhaps the CASE block you posted is being INSERTed into an int column?"

Yes, you're correct.  So, what syntax do I need to add?

Software Engineer
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
David Johnson, CD

do you want the value of the calculation of

12646–112521 = ‭-99,875‬?

or the string  12646–112521 converted into an int. (which will fail) 

Jim Horn

> "Perhaps the CASE block you posted is being INSERTed into an int column?"
>  Yes, you're correct.  So, what syntax do I need to add?

Ok.  Then what do you want to insert,  12646, 112521, 12646112521, or ( {David's post above} 12646–112521 =) ‭-99875‬ ?


Numeric data types can only have 0-9, decimal, NULL, or dash if it's the first character for a negative number.   No dashes in the middle.  So you'll have to tell us. 


> Ltrim(rtrim(ORDERLN.ITEMID)) + '–' + ltrim(rtrim(ORDERLN.LINEID)) ELSE '1' + '–' + ltrim(rtrim(ORDERLN.LINEID)) END as ITEMID,


Also keep in mind that if you're doing numerical expressions such as subtraction then you'll have to lose the single quote marks ' converting these values to text, and CAST() the Ltrim(rtrim(ORDERLN.ITEMID)) values as a number, SQL Server will interpret that as string concatenation and not number math. 


Software Engineer

ASKER
Hi Jim:

I deleted my table and recreated the ITEMID field as a varchar field, rather than an int field.  All is well, now.

Thank you!  Your guidance made me go down the successful path!

Software Engineer
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Software Engineer

ASKER
Thanks, Jim!