need "exact match within string"

[rusagar]

I know it is a little early for Scripting week but I cannot wait. This one has me stumped.

I need to find exact matches for search terms within a string. EG "cat" will find cat but not catastrophe from within a large string.

I cannot use "=" as that would match the whole string and I only want to find the exact match within the string.

Can anyone help me with the syntax here? I have racked my brains coming up with all sorts of convoluted solutions that all end up having a flaw. I have been looking around forum and it seems a few people are asking the same question but getting no answers. (you might see my question in some of them...) I am sure there must be some operator within sql as there is in FMP but I cannot find it.

Any Ideas?

Thanks all

[Ize]

    $needle = 'cat';
    $words = array('cat','catastrophe','cattle','catfish','catch','candle');
    foreach ($words as $word)
        print (strstr($word,$needle) ? 'true' : 'false' ) . '<br>'; 

Check out strstr()

[logic_earth]

$s = 'I lost my cat in a catastrophe event';
$find = 'cat';
 
if (preg_match('~\b' . $find . '\b~i', $s, $m)) {
    print 'Cat found.';
    print '<hr><pre>' . print_r($m, true) . '</pre>';
} else {
    print 'Cat not found.';
}

$m will contain an array with "cat" as the first element and an empty array if not found. Also will return true (1) if found and false (0) if not found.

http://php.net/preg_match

@ Ize
Your code doesn't work with the requirements, it matches every word but candle when it should not as stated above. "...will find cat but not catastrophe..."

Enless I am misreading and thats very possible it being so late.

[Raju Gautam]

But i prefer strcmp for your case rusagar if there is single word:

$needle = 'cat';
$words = array('cat','catastrophe','cattle','catfish','catch','candle');
foreach ($words as $word)
    print ((strcmp($word, $needle) == 0) ? 'true' : 'false' ) . '<br>'; 

Otherwise go for logic_earth's code regex.

[Ize]

@logic_earth:
It might be me who's misreading, but I thought Rusagar meant that it should also match substrings of the search query.

Maybe the topic starter can provide some clarity?

[rusagar]

I need to search a word with exact match or starting with the word in a string with single mysql query.

E.g select * from tablename where textfield like "%cam%.
This will return all the text id which cam is found anywhere in the single .

but i need to get the result that can be queried by splitting single words in a sentence.

id text
1 Camel_walk.
2 camel does drink water.
3 does Camel_store water.
4 In deset people can find_camel
5 this will not return

when i query for
select * from tablename where textfield like "%camel%.
return first 1,2,3,4

but i need to get the id in which the word start with camel
1)select * from tablename where textfield like "Camel%"
return 1,3 ( which is not working for me)
or even
2)select * from tablename where textfield like "%Camel"
return 4

These two queries is not working
Can some one help

Thanks

[rusagar]

hey guys ..... i need help in sql query to get my results on above posted examples not in php

Is it possible in sql??

[Ize]

I really lost you now

This:

select id from table where my_field like 'Camel%'

will find all records in which "my_field" start with the string "Camel".

select id from table where my_field like '%Camel'

will find all records in which "my_field" ends with the string "Camel".

select id from table where my_field like '%Camel%'

will find all records in which "my_field" contains the substring "Camel" anywhere in the string.

Hope this helps, if not; maybe you can provide some examples of records you want to find and clearly state the condition why these records should be found.

[rusagar]

Lets us suppose there are these records on the table named "test"
1 Camel_walk.
2 camel does drink water.
3 does Camel_store water.
4 In deset people can find_camel

Now, i need to search the exact word camel from above table i.e the query should return record 2 not others because record 2 has exact word "camel" which i m searching.

Hope this can give u an idea
Thanks

[Raju Gautam]

Hello rusagar,

Did you check MySQL FULL TEXT SEARCH? I think there might be something such search facility though i have not gone through all of its types. But i hope you can have this facility there.

http://dev.mysql.com/doc/refman/5.0/...xt-search.html

[Ize]

Ah, thanks for making that clear
You could search for "% Camel %" (note the spaces), but that wouldn't work when the substring is at the beginning or end of the string.
You're gonna need a regular expression:

SELECT id FROM test WHERE my_field REGEXP '[[:<:]]camel[[:>:]]'

See also: http://dev.mysql.com/doc/refman/5.0/en/regexp.html

[rusagar]

Thanks u all for your valuable suggestions
Rudra
www.rusagar.com