SQL query with mysql variable in like statement?

Question

I want to select/check a value in a text column based on another column value like

SELECT tableA.randomID, wp_posts.post_content, wp_posts.ID
FROM tableA, wp_posts 
WHERE wp_posts.post_content LIKE '%tableA.randomID%' 
AND tableA.randomID = '110507B2VU'

But that didn't work, how do i set the LIKE statement

This doesn't work:

SELECT tableA.randomID, wp_posts.post_content, wp_posts.ID
FROM tableA, wp_posts 
WHERE wp_posts.post_content LIKE '%110507B2VU%'

Answer 1

When you enclose something in quotes - its taken as a literal value, so in your first query - where you put LIKE '%tableA.randomID%' - MySQL is actually treating that as a string.

Without the quotes, it will take the value - ie:

WHERE something LIKE tableA.randomID

This will actually compare 'something' to the value of tableA.randomID, rather than the literal string.

To then include your % wildcards to make your LIKE statement different to an 'equals' comparison, try the CONCAT() function.

WHERE something LIKE CONCAT("%",tableA.randomID,"%")

Which will, if the value of tableA.randomID was say.. 'banana' actually end up being this:

WHERE something LIKE '%banana%'

I hope I explained that clearly enough ;-)

Answer 2

you can use the concat() with like

like concat('%',variable_name,'%')

In your case

SELECT tableA.randomID, wp_posts.post_content, wp_posts.ID
FROM tableA, wp_posts 
WHERE wp_posts.post_content LIKE CONCAT('%',tableA.randomID,'%')
AND tableA.randomID = '110507B2VU'