Which version supports concat???

[Don Vaillancourt]

I have a problem with different versions of MySQL supporting concat
differently.

The portion of the query that uses the concat function looks similar to
the one below:

select *
from table 1
where 'value' like concat('%' , a)
or 'value' like concat( a, '%')
or 'value' like concat('%' , a, '%')

The alternative query is:

select *
from table 1
where 'value' like '%' + a
or 'value' like a + '%'
or 'value' like '%' + a + '%'

The concat function works differently in different servers and oddly
enough seems to work flawlessly on the older version. Is there a switch
that controls this function. I haven't been able to find one.


MySQL Server 1 - running on Windows XP Pro: 4.1.13a-nt

The above version supports concat and returns expected rows from the
database.

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.

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.

[Bill Karwin]

"Don Vaillancourt" <donv@webimpact.com> wrote in message
news:3eWVf.16144$43.10004@nnrp.ca.mci.com!nnrp1.uu net.ca...

>I have a problem with different versions of MySQL supporting concat
> differently.

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
MySQL.
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 documentation 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?

Regards,
Bill K.

[Don Vaillancourt]

Well after playing around with my query I figured out that:

select * from table1 where a like '%,' + b

was being interpreted as:

select * from table1 where a like '%'

which is why I was getting my rows back.

So thank you.

So I decided to go back to the very beginning and looked at why were
were doing this. The original idea was to do this:

select * from table1 where b in a

where 'a' is a list of numbers in a varchar.

Do you know of a way to use a string list of numbers with an IN operator?

Thanks


Bill Karwin wrote:

> "Don Vaillancourt" <donv@webimpact.com> wrote in message
> news:3eWVf.16144$43.10004@nnrp.ca.mci.com!nnrp1.uu net.ca...

>> I have a problem with different versions of MySQL supporting concat
>> differently.

>
> 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
> MySQL.
> 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 documentation 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?
>
> Regards,
> Bill K.
>
>

[Bill Karwin]

"Don Vaillancourt" <donv@webimpact.com> wrote in message
news:ArYVf.16153$43.2786@nnrp.ca.mci.com!nnrp1.uun et.ca...

> select * from table1 where b in a
>
> where 'a' is a list of numbers in a varchar.
>
> Do you know of a way to use a string list of numbers with an IN operator?

This is not the recommended way of testing whether a value is in a set in
SQL. The preferred way is to create another table, in which each value in
your `a` list gets its own record. Then you can test if the value of `b` is
in that list, simply by doing a JOIN. Another advantage of using a JOIN is
that the optimizer can make use of indexes. However, when doing
pattern-matching in the middle of strings, indexes usually cannot help.

Anyway, that's not what you asked.

There are several functions in MySQL to test if a string contains a certain
substring. LOCATE() and INSTR() are two examples.

It's tricky in this kind of pattern matching, for example, to prevent a
value of 23 in `b` from matching 1234 in `a`. Make the value in `b` have a
comma character before and after as delimiters, and also put a comma before
and after `a` to avoid beginning-and-end cases.

WHERE INSTR(CONCAT(',', a, ','), CONCAT(',', b, ','))

Thus ',23,' is a substring of ',23,45,129,456,1234,'.

For more on string functions, see:
[url]http://dev.mysql.com/doc/refman/5.0/en/string-functions.html[/url]

There's also regular expression matching:

WHERE a RLIKE CONCAT('[[:<:]]', b, '[[:>:]]')

Thus the string '23,45,129,456,1234,' matches the regular expression
'[[:<:]]23[[:>:]]'

For more on regular expressions, see:
[url]http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html[/url]
[url]http://dev.mysql.com/doc/refman/5.0/en/regexp.html[/url]

Regards,
Bill K.

[Don Vaillancourt]

Yeah, I know its not the best solution.

But I finally did resolve the issue.

After putting thet concats back in, and doing more test what was
happening is that the list had a space in it, so that the pattern used
by LIKE wasn't matching.

So the query was:

select * from '111, 222,333' like '%,222,%'

And would never return anything.

So the final solution was:

select * from replace('111, 222,333', ' ' , '') like '%,222,%'

I know it's not the best way to do queries, but based on the data I was
given, this was the best solution.

Thanks for putting me on the right path.



Bill Karwin wrote:

> "Don Vaillancourt" <donv@webimpact.com> wrote in message
> news:ArYVf.16153$43.2786@nnrp.ca.mci.com!nnrp1.uun et.ca...

>> select * from table1 where b in a
>>
>> where 'a' is a list of numbers in a varchar.
>>
>> Do you know of a way to use a string list of numbers with an IN operator?

>
> This is not the recommended way of testing whether a value is in a set in
> SQL. The preferred way is to create another table, in which each value in
> your `a` list gets its own record. Then you can test if the value of `b` is
> in that list, simply by doing a JOIN. Another advantage of using a JOIN is
> that the optimizer can make use of indexes. However, when doing
> pattern-matching in the middle of strings, indexes usually cannot help.
>
> Anyway, that's not what you asked.
>
> There are several functions in MySQL to test if a string contains a certain
> substring. LOCATE() and INSTR() are two examples.
>
> It's tricky in this kind of pattern matching, for example, to prevent a
> value of 23 in `b` from matching 1234 in `a`. Make the value in `b` have a
> comma character before and after as delimiters, and also put a comma before
> and after `a` to avoid beginning-and-end cases.
>
> WHERE INSTR(CONCAT(',', a, ','), CONCAT(',', b, ','))
>
> Thus ',23,' is a substring of ',23,45,129,456,1234,'.
>
> For more on string functions, see:
> [url]http://dev.mysql.com/doc/refman/5.0/en/string-functions.html[/url]
>
> There's also regular expression matching:
>
> WHERE a RLIKE CONCAT('[[:<:]]', b, '[[:>:]]')
>
> Thus the string '23,45,129,456,1234,' matches the regular expression
> '[[:<:]]23[[:>:]]'
>
> For more on regular expressions, see:
> [url]http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html[/url]
> [url]http://dev.mysql.com/doc/refman/5.0/en/regexp.html[/url]
>
> Regards,
> Bill K.
>
>

[Bill Karwin]

"Don Vaillancourt" <donv@webimpact.com> wrote in message
news:XBZVf.16155$43.6021@nnrp.ca.mci.com!nnrp1.uun et.ca...

> select * from replace('111, 222,333', ' ' , '') like '%,222,%'

Question: how do you match the 111 entry this way?
In your list, 111 does not have a comma preceding it.

Regards,
Bill K.