Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    2

    Unanswered: Can someone help in this?

    I am having records in a field (field_id) of a table (in mysql database), which is having ID stored per line, for example

    1
    2
    3
    10
    11
    13



    above is one record, and I have many records similar to that.

    and, when I am writing the query SELECT * FROM $table_name where field_id like '%1%'

    then the rows having the results like
    10
    11
    13


    are also returned. I am upset. How to overcome this problem?

    I found one fix to this, but it fails in most of the conditions, this is -

    SELECT * FROM `$table_name` WHERE field_id like '%\n1%' OR '%1\n%'

    This fails, when the (field_id) have only one record, for example, '1'. I want to catch everything, where line equals to 1, whether single line in the record, or many.

    I am going mad for this.

    Can someone provide me any way? Thanks.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by mohansinfh View Post
    I am upset. How to overcome this problem?
    Upset: Have you tried Xanax?
    SQL: can you reword this question to make it easier to understand?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my sincere advice: normalize your data

    otherwise your query will always perform a table scan, i.e. it will start off really slow and get slower and slower the more rows you have

    but if you insist on keeping the current design, try this --
    Code:
    WHERE CONCAT(',',REPLACE(REPLACE(field_id,'\r',''),'\n',','),',') LIKE '%,1,%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •