Double use of operator '+' in MS TSQL

In our software users can define various mapping functions which are partly predefined but partly require the formulation of TSQL expressions. In the case of TSQL, the double use of the '+' operator raises problems. As long, as one of the operands is numeric, the '+' is interpreted as addition. That means, that the other operands are automatically converted to numeric.

The problem arises, if the '+' is not supposed to denote an addition but a concatenation. Then normally a runtime error occurs because the operands are not necessarily all numeric. Of course, it is even worse if no error occurs when all operands are numeric and the addition is performed instead of the concatenation.

It seems to be impossible to tell the system that a concatentation has to be performed rather than an addition. One possibility would be  to request the users to cast all operands explicitly to varchar - then the '+' is interpreted as concatenation. But this would require much more "computer science knowledge" by the users, thus we try to avoid that.

Are there other operators for these operations, or are there other mechanisms which might help?

Thanks for your support.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
in sql, you would need to explicitely cast the numerical field to varchar:

CAST( your_int_field AS VARCHAR(100)) + your_varchar_field

but as you know that already, and as there is no other option (other than converting all fields to varchar, but that could give other problems), the solution has to be "education" of the users...
or get another software  ...
Well, you could tell  them to use a harmless string scalar function (like RTRIM) with every operand.   That would pretty much guarantee that the result is a varchar.

...but you gotta admit, this is a rather strange question

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ephraim WangoyaCommented:

This is more of an application design issue rather than a SQL problem
Each of the records should really contain an identifier of what operation you are to execute
Your class instance in the application should then be ably to inter prate what operation to perform

Database job is to store your data, what you do with the data is up to your application
wowi12Author Commented:
Well, I got my answer that there is a problem with TSQL. The problem is that the function does not know what the user wants because it is a generic function which takes any TSQL expression. Obviously, we have to consider to not rely on TSQL here but develop our own language to give the users better means to express what they want.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.