FerencMantfeld
2nd March 2010, 07:14 PM
It is my opinion that whoever thought that using a + symbol for concatenation in SQL was a good idea, needs to publicly flogged!
In Sql Server, it you perform the following queries, you get the following results:
Query Result
select 15 + 25 40
select '15' + '25' 1525
select '15' + 25 40 /* Note here that '15' is string and 25 is numeric */
This third one is really disturbing, as you would need to be familiar with the data in order to prevent addition where concatenation is desired.
There are 2 ways to work around this shortsightedness:
select 15 + cast(25 as varchar)
or
select 15 + convert(varchar,25)
Adapt this to your queries replacing the literals with the actual columns (where numbers are stored in strings).
A classic example of this might be where the date parts are stored in 3 separate columns (year, month, day), which btw is very bad design, but worse things have happened. If you want to ensure that concatenating the 11th of Jul 2009 returns 20090711 rather than 2027, cast all 3 of the components to varchar, as in:
select cast(year as varchar) + cast(mm as varchar) + cast(day as varchar) ....
For what my opinion is worth, the pipe | symbol used in Oracle and some other RDBMS, makes so much more sense, not too much chance of confusion between concatenation and addition.
In Sql Server, it you perform the following queries, you get the following results:
Query Result
select 15 + 25 40
select '15' + '25' 1525
select '15' + 25 40 /* Note here that '15' is string and 25 is numeric */
This third one is really disturbing, as you would need to be familiar with the data in order to prevent addition where concatenation is desired.
There are 2 ways to work around this shortsightedness:
select 15 + cast(25 as varchar)
or
select 15 + convert(varchar,25)
Adapt this to your queries replacing the literals with the actual columns (where numbers are stored in strings).
A classic example of this might be where the date parts are stored in 3 separate columns (year, month, day), which btw is very bad design, but worse things have happened. If you want to ensure that concatenating the 11th of Jul 2009 returns 20090711 rather than 2027, cast all 3 of the components to varchar, as in:
select cast(year as varchar) + cast(mm as varchar) + cast(day as varchar) ....
For what my opinion is worth, the pipe | symbol used in Oracle and some other RDBMS, makes so much more sense, not too much chance of confusion between concatenation and addition.