1

I tried using LIKE and CONTAINS, but neither worked

My column named Column in my table contains "asdl, test3, asdklj, dlksj" and I want to find "test3" in there using this string "test2, test3, test4"? How can I do this?

$string = "test2, test3, test4";

"SELECT * FROM table WHERE CONTAINS(Column, ".$string.")"

Another option is to use LIKE, but doesn't work either:

$string = "test2, test3, test4";

"SELECT * FROM table WHERE Column LIKE '%".$string."%'"

**

In summary I need to find a word in $string that matches with a word in Column.

**

ruyy
  • 31
  • 1
  • 7
  • In MySQL the `CONTAINS` function is for dealing with spatial data and does not work for strings. As suggested, use `LIKE`. If you have tagged incorrectly and are using the SQL Server `CONTAINS` function to work with full text indexes, then you need to separate your terms with `OR` rather than a comma, e.g. `WHERE CONTAINS(Column, "test2 OR test3 OR test4")` – GarethD Jan 08 '16 at 11:33
  • What you are trying to do is not easily possible, but also suggests that your database is poorly normalised (ie, having a comma separated list in a string field is normally not a good idea). You need to split up the comma separated fields, either in the database design, in the PHP before using SQL or in the actual SQL statement. Following your edit it is now obvious that this is not a duplicate of the question this was pointed at. – Kickstart Jan 08 '16 at 11:45

1 Answers1

1

Can be done using like. If your strings are comma seperated values then do it like this:

SELECT
  * FROM table
WHERE
  column LIKE "test3,%" OR
  column LIKE "%,test3,%" OR
  column LIKE "%,test3"
maxhb
  • 8,554
  • 9
  • 29
  • 53
  • It's a variable I want to search in. How do I seperate each word from the variable to find test3 in it? Look at edited post. – ruyy Jan 08 '16 at 11:40
  • 1
    @GarethD That's plain wrong. Just try `SELECT if("aaa,test30,bbb" LIKE "test3,%" OR "aaa,test30,bbb" LIKE "%,test3,%" OR "aaa,test30,bbb" LIKE "%,test3", 'Matches', 'Does not match')` – maxhb Jan 08 '16 at 11:40
  • I only have "test2, test3, test4". I can't separate "test3" from the string. I need to find a similar word in Column "asdl, test3, asdklj, dlksj" and my string "test2, test3, test4", SQL needs to automaticly take out "test3". – ruyy Jan 08 '16 at 11:45
  • OK, so you are trying to find any of the strings within "test2,test3,test4" in your table column?! – maxhb Jan 08 '16 at 11:48
  • Explode out the search string, and from that generate a sub query to do a JOIN using FIND_IN_SET. Horrible idea, but with fiddles (ie, you might need to set the collation manually in the sub query) something like this - _$field=explode(', ', $string); $subquery = "(SELECT '".implode(" AS aword UNION SELECT ", $field)."' AS aword)"; $sql = "SELECT * FROM table INNER JOIN $subquery sub1 ON FIND_IN_SET(sub1.aword, table.Column)";_ – Kickstart Jan 08 '16 at 11:53
  • Use `explode(',',$string)` and `trim()` to split `$string` into substrings. Then use above sql. Maybe extend it to accepts lines which match ANY of your substrings by adding more `or column like"..."` – maxhb Jan 08 '16 at 11:54
  • @maxhb, sorry, you are right. More coffee required I think. I have removed my earlier comment. – GarethD Jan 08 '16 at 11:54