Professional Web Applications Themes

Which version supports concat??? - MySQL

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. ...

  1. #1

    Default Which version supports concat???

    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.
    Don Vaillancourt Guest

  2. #2

    Default Re: Which version supports concat???

    "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
    > 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 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?

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: Which version supports concat???

    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" <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
    >> 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 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?
    >
    > Regards,
    > Bill K.
    >
    >
    Don Vaillancourt Guest

  4. #4

    Default Re: Which version supports concat???

    "Don Vaillancourt" <donvwebimpact.com> wrote in message
    news:ArYVf.16153$43.2786nnrp.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 Guest

  5. #5

    Default Re: Which version supports concat???

    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" <donvwebimpact.com> wrote in message
    > news:ArYVf.16153$43.2786nnrp.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 Guest

  6. #6

    Default Re: Which version supports concat???

    "Don Vaillancourt" <donvwebimpact.com> wrote in message
    news:XBZVf.16155$43.6021nnrp.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.


    Bill Karwin Guest

Similar Threads

  1. Concat two fields in SQL
    By thomascraig in forum Coldfusion Database Access
    Replies: 7
    Last Post: June 9th, 01:54 PM
  2. Concat problem with SQL
    By Shivaan Keldon in forum Coldfusion Database Access
    Replies: 4
    Last Post: October 24th, 12:14 PM
  3. contribute version that supports Hebrew web pages
    By inigo21 in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: September 21st, 06:25 AM
  4. php string concat
    By anybody in forum PHP Development
    Replies: 2
    Last Post: October 14th, 03:04 PM
  5. Replies: 0
    Last Post: September 26th, 01:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139