0

I am trying to search in MySQL FT index for strings that start with 'Ford' to be able to find string like 'Ford', 'Ford1', 'Ford3', 'FordFord' etc... (this is why I am using '*'):

SELECT DISTINCT CARS.car
FROM CARS_FT_INDEX CARS_IDX
WHERE ( MATCH ( CAR_NAME ) AGAINST ( "/+FORD*/" IN BOOLEAN MODE) )

However, it's possible that the search term will contain '@' or other special character ("/+FORD@*/") which will resolve with the error message:

SQL Erorr (1064): syntax error, unexpected '@', expecting $end

I was suggested to surround the term with with ' ('"/+FORD@*/"').

But the search term :AGAINST ( '"/+FORD@*/"' IN BOOLEAN MODE) (with '@' and '') don't act as "start with" now (only exact match) and I am unable to search for multiple words ('"/+FORD1 +FIAT*/"').

I tried many variations (including CONCAT) and tried to search in google but couldn't find any solution here.

I will appreciate your advice here.

Thanks, Mike

Mike
  • 1,007
  • 2
  • 16
  • 33
  • `WHERE ( MATCH ( CAR_NAME ) AGAINST ( "+FORD*" IN BOOLEAN MODE) )` should work – Raymond Nijland Apr 04 '19 at 10:52
  • Also see [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) for providing a example data and expected results. – Raymond Nijland Apr 04 '19 at 10:54
  • It works but `WHERE ( MATCH ( CAR_NAME ) AGAINST ( "+FORD@*" IN BOOLEAN MODE) )` (with `@`) produce the error that I mentioned. – Mike Apr 04 '19 at 10:55
  • User may add special characters and I would like to prevent failures. User may add string in any language so it will be hard to omit character. – Mike Apr 04 '19 at 10:57
  • *"(with @) produce the error that I mentioned."* Why do you want to use `@` ? It has no operator meaning with full text searching you are wrongly adviced to use `@` by somebody. – Raymond Nijland Apr 04 '19 at 10:58
  • *"User may add special characters and I would like to prevent failures. User may add string in any language so it will be hard to omit character."* Well `FÖRD` is indeed not `FORD` – Raymond Nijland Apr 04 '19 at 10:58
  • I can remove `@` from user's search term but I can't be sure that there are other problematic special characters that I missed (like `(`, `~`). – Mike Apr 04 '19 at 10:59
  • *"I can remove @ from user's search term but I can't be sure that there are other problematic special characters that I missed (like (, ~)."* Yes you can but i hope the user is so smart to **not** search a car make/model with special characters.. Also you are making a "blacklist" here off special characters which to remove which can be huge considering the utf8 charset – Raymond Nijland Apr 04 '19 at 11:05
  • White list is indeed a problem. I just checked and `@` ca be easily added to FT index so I believe that we should allow him to search for problematic characters. – Mike Apr 04 '19 at 11:10
  • *" I just checked and @ ca be easily added to FT index so I believe that we should allow him to search for problematic characters."* Yes because full text search is a **literal** search, when running in boolean mode there is boolean based search engine on top off that.. i wanted to mention that also in mine other comments but i didn't because i assumed you already knew that. – Raymond Nijland Apr 04 '19 at 11:27
  • Thanks you Raymond! I understand that `@` is not allowed (I will omit this character). But, are there any other characters that not allowed? This query also failing when I am using `~` or `(` instead of `@`. – Mike Apr 08 '19 at 05:26

1 Answers1

0

It seems that it is not possible or too complicated.

I decided to omit special characters from the search term as described here: https://mariadb.com/kb/en/library/full-text-index-overview/#in-boolean-mode

Thanks any way! Mike

Mike
  • 1,007
  • 2
  • 16
  • 33