Concatenate string with field value in MySQL

Question

I have the need to concatenate a string with a field value in a MySQL query in order to LEFT JOIN two tables.

Table one has a column called "category_id" with numeric values, such as 61, 78, 94 and such.

Table two has a column called "query" which refers to a request route mechanism, and it has values such as "product_id=68", "category_id=74", "manufacturer_id=99" and so on.

So in my query I require to join the tables when ever a concatenated string derived from a set string and the value of the "category_id" column matches the query field.

My SQL statement is currently:

SELECT * FROM tableOne 
LEFT JOIN tableTwo
ON tableTwo.query = 'category_id=' + tableOne.category_id

I have tried using the || operator instead of the + operator, but still no luck. Is it possible to do this in MySQL, or have I jumped the gun here?

Will · Accepted Answer · 2016-02-13T20:31:16.163

103

Have you tried using the concat() function?

ON tableTwo.query = concat('category_id=',tableOne.category_id)

edited Feb 13 '16 at 20:31

answered Oct 09 '13 at 02:01

Will

2,343
1
14
14

Perfect! I had tried using `CONCAT_WS('=', 'category_id', tableOne.category_id)`, but it seems that was the wrong function entirely! Thank you very much! – Ben Oct 09 '13 at 02:16
This is fine. But how can I concatenate on both side of field. like adding quotes on both sides of menuid for eg: "menuid1".? – ShivarajRH Feb 27 '14 at 09:32
6

Since concat() accepts an arbitrary number of arguments (http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_concat), you should be able to concat('"',table.column,'"'); – Will Feb 27 '14 at 23:51

score 10 · Answer 2 · answered Oct 09 '13 at 02:06

10

SELECT ..., CONCAT( 'category_id=', tableOne.category_id) as query2  FROM tableOne 
LEFT JOIN tableTwo
ON tableTwo.query = query2

answered Oct 09 '13 at 02:06

chadzheng

161
4

Thank you for your answer, this is an elegant solution! – Ben Oct 09 '13 at 02:17

score 9 · Answer 3 · edited May 23 '17 at 11:33

9

Here is a great answer to that:

SET sql_mode='PIPES_AS_CONCAT';

Find more here: https://stackoverflow.com/a/24777235/4120319

Here's the full SQL:

SET sql_mode='PIPES_AS_CONCAT';

SELECT * FROM tableOne 
LEFT JOIN tableTwo
ON tableTwo.query = 'category_id=' || tableOne.category_id;

edited May 23 '17 at 11:33

Community

1
1

answered May 10 '16 at 18:51

David Namenyi

697
9
9

score 3 · Answer 4 · answered Oct 09 '13 at 02:05

3

MySQL uses CONCAT() to concatenate strings

SELECT * FROM tableOne 
LEFT JOIN tableTwo
ON tableTwo.query = CONCAT('category_id=', tableOne.category_id)

answered Oct 09 '13 at 02:05

Chris

94
3

Concatenate string with field value in MySQL

4 Answers4

Linked

Related