> "Don Vaillancourt" <donvwebimpact.com> wrote in message
> news:3eWVf.16144$43.10004nnrp.ca.mci.com!nnrp1.uu net.ca...
>> I have a problem with different versions of MySQL supporting concat
> I've used CONCAT() on MySQL 3.23, 4.0, 4.1, and 5.0. In my experience, it
> has worked identically in all versions.
> However, as far as I know, + is never a string concatenation operator in
> In fact, this feature was requested and turned down.
> See [url]http://bugs.mysql.com/bug.php?id=2963[/url]
> So I'd be interested in hearing how this is possible on your servers. Did
> you make any source-level customizations to MySQL?
> Using + for string concatenation is not standard SQL; it is notably
> supported by Microsoft SQL Server products. It may be supported by some
> other RDBMS brands too, but I can't find any doentation that suggests it
> is supported by MySQL, nor does it work when I try it.
> MySQL does support the || operator for string concatenation, in conformance
> with the ANSI SQL syntax. But you have to set a SQL mode to enable this
> behavior. This could certainly be set differently on different servers.
> See [url]http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html[/url]
>> MySQL Server 2 - running on Windows XP Pro: 4.1.9-nt
>> The above server had problems working with the concat and so we had to
>> use the plus '+' sign. But I don't recall the problem we had.
> It would be useful to know what problem you had with CONCAT() on this
> server. Can you try out a few tests? Try connecting the to "test" database
> on each of your servers, and try these queries:
> SELECT CONCAT('a', 'b', 'c'); -- should return 'abc'
> SELECT CONCAT('a', NULL, 'c'); -- should return NULL
> SELECT 'a' + 'b' + 'c'; -- should return 0, and warn "Truncated incorrect
> DOUBLE value" for each term.
> SELECT 'a' + NULL + 'c'; -- should return NULL, and warn "Truncated
> incorrect DOUBLE value" for each non-NULL term.
>> MySQL Server 3 - running on Redhat Enterprse Server: 4.1.8-standard-log
>> The above server executes queries that use concat, but does not return
>> any rows. I had to replace concat('a' , 'b', 'c') to 'a' + 'b' + 'c'
>> and this worked.
> Interesting. I've never seen this syntax work as string concatenation on
> MySQL. The expression is legal, but it is an arithmetic expression. Each
> string is converted to a float value (truncating irrelevant characters), and
> then the three values are summed. In the case of 'a' + 'b' + 'c', the
> result will be 0+0+0 = 0.
> Note that in string concatenation, if either operand is NULL, the result of
> the operation is NULL. This is standard SQL semantics. So the state of
> your data could make your expression fail to return any rows. If a is NULL,
> then CONCAT('%', a) will be NULL. And using this in a LIKE predicate will
> always be false, so no rows will be returned.
> Do you have any rows in your table where the `a` column is NULL?
> Bill K.