substring matching with mysql?

[Anon]

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

[hometoast]

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

[Anon]

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

[mrskhris]

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

[Anon]

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/

[Anon]

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/