substring matching with mysql?
Results 1 to 6 of 6

Thread: substring matching with mysql?

  1. #1
    Senior Member
    Join Date
    Jun 2000
    Posts
    65,357

    substring matching with mysql?

    I've got a comma delimited list that I'd like to match against a single value in a mySQL table:

    e.g.:

    $commaList="1,2,4"

    and I select all rows which have a particular column with a value contained in that list.

    is there some mysql function that allows for substring matching? LIKE isn't working, though I could be using it incorrectly...

    thanks much
    adam altman

  2. #2

    RE: substring matching with mysql?

    Do you mean you want to search for "1 or 2 or 4" in one particular field (such as an index number)? if thats the case you can do:
    $searcharray = explode(",", $commaList);
    then loop through the array and add LIKE and = operators to the WHERE clause. i.e. WHERE x='1' or x='2' or x='3'.
    Also, if you're like me, and you are moving from ASP/access to MySQL remember that the LIKE wildcard character is '%' and not '*'.

    Hope that helps.

    -dan

  3. #3
    Senior Member
    Join Date
    Jun 2000
    Posts
    65,357

    RE: substring matching with mysql?

    Ahh yes -- this works. Hadn't thought about using PHP to dynamically build the query -- now doing

    $expand_array=explode(",",$expand);
    if (count($expand_array)==0) {
    $expand_filter = " AND 1=1 ";
    } else {
    for ($i=0; $i<count($expand_array); $i++) {
    if ($i==0) { $expand_filter=" AND "; }
    $expand_filter.= "thread = $expand_array[$i]";
    if ($i<count($expand_array)-1) { $expand_filter.= " OR ";}

    }
    }

    or something to that effect.

    thanks much!
    adam

  4. #4
    Senior Member
    Join Date
    May 2001
    Posts
    114

    RE: substring matching with mysql?

    Little remark: If this field has integer/float/number type, you can simply use "in": where x in ($your_comma_delimited_list)

  5. #5
    Senior Member
    Join Date
    Jun 2000
    Posts
    65,357

    RE: substring matching with mysql?

    I think you need something like follows. I have table 'words' (in MysQL), which contains single words, and string $sentence='Sentence which contains few words'. To find, which words in $sentence i have also in table, use query like follows:

    SELECT * FROM words WHERE '$sentence' LIKE concat('%',word,'%');

    The trick is in order of the syntax, expression must be before 'LIKE' and pattern after it.


    Have Fun
    Eero
    http://www.webwhistler.com/

  6. #6
    Senior Member
    Join Date
    Jun 2000
    Posts
    65,357

    RE: substring matching with mysql?

    Forgot: 'word' is a field in table 'words' in this example.


    Eero wrote:
    -------------------------------
    I think you need something like follows. I have table 'words' (in MysQL), which contains single words, and string $sentence='Sentence which contains few words'. To find, which words in $sentence i have also in table, use query like follows:

    SELECT * FROM words WHERE '$sentence' LIKE concat('%',word,'%');

    The trick is in order of the syntax, expression must be before 'LIKE' and pattern after it.


    Have Fun
    Eero
    http://www.webwhistler.com/

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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