MySQL: Using Column Name Inside The LIKE Keyword

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

Related Posts Plugin for WordPress, Blogger...

10 comments

  1. 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.

  2. 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.

  3. 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!

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *