I cannot figure out how to query a table on a column being contained in a string.
Table companies:
id | title
----------
1 | abc
2 | abcd
3 | bcde
4 | xyfa
I have tried:
SELECT * FROM companies WHERE CONTAINS("ABCDEFG Ltd", title)
SELECT * FROM companies WHERE CONCAT("%", title, "%") LIKE "ABCDEFG Ltd"
I would like to return:
id | title
----------
1 | abc
2 | abcd
3 | bcde
I think you are looking for this:
SELECT *
FROM companies
WHERE 'ABCDEFG Ltd' LIKE CONCAT('%', title, '%');
That is, the pattern is whatever is in title with wildcards on either side. You want to know if your company name matches it.
An alternative to Gordon's answer would be INSTR function
WHERE INSTR(title, 'ABCDEFG Ltd') <> 0
Normally, I recommend against function calls in WHERE conditions if possible, but putting a field on the right hand side of a LIKE and/or CONCATing it is just as bad; but in this case one or the other is unavoidable. I can't say for sure which version will be faster, you should try both. I would guess mine might be, since the like-concat likely involves a string copy then a compare; whereas the INSTR should only need a compare; but with MySQL it is not always obvious.
CONTAINS()version is going to throw an error (unless you have created a user-defined function named of that name, since that's not a builtin function of MySQL). The query with theLIKEcomparison will execute, but it's not going to return any rows. It doesn't matter what value is found in thetitlecolumn, the comparison never going to find a literal percent character'%'at the as the first character in the literal string'ABC...'. (The percent character is only a wild card when it appears in the string on the right side of the LIKE comparison.)