MySQL: Using Column Name Inside The LIKE Keyword

Posted by blogmeister 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

Share the post "MySQL: Using Column Name Inside The LIKE Keyword"

Related Posts Plugin for WordPress, Blogger...

tags: , ,

  • Hioushi

    Thanks! I was just thinking about this, nice solution.

  • http://cavalcante.net/post/Tech-Ed-Brasil-2009.aspx Tyrone Turnow

    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.

  • http://www.in.gr Vassilis

    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.

  • http://www.flashgameskid.info/ flash games

    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!

  • voodoo

    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),”%”)

  • steve

    @voodoo

    have you try this

    LIKE CONCAT(‘%’,table.column_name,’%')

  • http://bestmacgames.us best mac games

    great post..thanks for share!

  • greenpeace

    WOW!! IT WORKS FOR ‘JOIN’ TO!
    THANKS IT VERY USEFUL

  • cvg

    nice!
    CONCAT(‘this is extra stuff i am writing’, ‘because this website doesn’t allow short yet meaningful comments’);

  • nab

    THANKS A LOT IT WORKS