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?
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
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  ...
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.