MySQL: Using Column Name Inside The LIKE Keyword
Posted by tech on
February 22, 2009
When using the LIKE keyword, I always get to do it this way, LIKE (‘%STRING%’). When the time came for me to use a column name instead since all string values to search for is in a table in my database, I tried to just replace the string value with the column name like this LIKE (%table.column_name%). The result was pretty obvious. It gave me an error.
As a string is required in this situation, I thought of trying the CONCAT() function since that outputs a string. And it worked! Hehehe. You can do it like this
1 | LIKE CONCAT('%', table.column_name) |
February 16th, 2010 at 12:04 am
Thanks! I was just thinking about this, nice solution.
April 10th, 2010 at 2:08 am
I don’t agree with everything in this posting, but you do make some very good points. I’m very interested in this topic and I myself do alot of research as well. Either way it was a well thoughtout and nice read so I figured I would leave you a comment. Feel free to check out my website sometime and let me know what you think.
November 10th, 2010 at 3:38 pm
Very good solution,
Thank You!
Thanks this idea I solve a particular case.
I have 2 tables: pdfdoc AS p, users AS u.
When a user download a pdf_file I put the id of pdf_file into a text column named ‘u.id_pdf_downloaded’ separated by 2 poits: example : “.345.356.56.78.9.876.” throught an
UPDATE users SET id_pdf_downloaded CONCAT(id_pdf_downloaded, ‘.$idpdf_download_now.’) WHERE id = ‘$iduser’
Now I need to make a query to NOT have in my array the id of pdf_file that are in ‘id_pdf_downloaded’ column.
the right syntax in WHERE is:
WHERE id = ‘$iduser’
…AND …
u.id_pdf_viewed NOT LIKE CONCAT(‘%.’, p.idpdf, ‘.%’)
AND … ecc.
words:
column_name in LIKE
I tested and it works good.
I hope help anyone!
Vassilis,
Athens.
Milan.
December 20th, 2010 at 3:19 am
As Vassilis above I needed the same thing with categories for games. My query will be like this:
SELECT
id, name_en, name_de,
(SELECT COUNT(id) FROM flash_games WHERE category LIKE CONCAT(“%,”, flash_games_cat.id, “,%”)) AS nr_games
FROM
flash_games_cat
ORDER BY
name_en
Thank you!
February 25th, 2011 at 2:19 am
Great post.
If you need to like %string% you use a double concat. The example from flash games wont work.
LIKE CONCAT(CONCAT(“%”,table.colum_name),”%”)
March 7th, 2011 at 7:50 pm
@voodoo
have you try this
LIKE CONCAT(‘%’,table.column_name,’%')
October 9th, 2011 at 11:14 am
great post..thanks for share!