Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Concatenate string with field value in MySQL [duplicate]

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?

like image 326
Ben Avatar asked Oct 09 '13 01:10

Ben


People also ask

How do I concatenate text from multiple rows into a single text string in MySQL?

The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value.

Can you use || in MySQL?

The || , operator is a nonstandard MySQL extension. As of MySQL 8.0. 17, this operator is deprecated; expect support for it to be removed in a future version of MySQL. Applications should be adjusted to use the standard SQL OR operator.


4 Answers

Have you tried using the concat() function?

ON tableTwo.query = concat('category_id=',tableOne.category_id)
like image 104
Will Hannah Avatar answered Oct 05 '22 20:10

Will Hannah


SELECT ..., CONCAT( 'category_id=', tableOne.category_id) as query2  FROM tableOne 
LEFT JOIN tableTwo
ON tableTwo.query = query2
like image 33
chadzheng Avatar answered Oct 05 '22 22:10

chadzheng


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;
like image 38
David Namenyi Avatar answered Oct 05 '22 21:10

David Namenyi


MySQL uses CONCAT() to concatenate strings

SELECT * FROM tableOne 
LEFT JOIN tableTwo
ON tableTwo.query = CONCAT('category_id=', tableOne.category_id)
like image 36
Chris Avatar answered Oct 05 '22 21:10

Chris