Re: Truncate operator in quoted expression?

[Mike Ruskai]

Standardizing the data isn't an option. I only used street addresses as an example. There are other fields where there is no standardized format.

In any case, according the documentation, when searching for a quoted expression, it basically treats it as two separate fulltext searches, then scans the result set for the quotes expression. In other words, using this:

where match(field) against('"foobar jones"')

...is essentially the same as using this:

where match(field) against('foobar') or match(field) against('jones')

...then doing this against the result set:

where field like '%foobar jones%'

But the following is also possible:

where match(field) against('foobar') or match(field) against('jone*')

Assuming the above to be creating a temp table, one could then do:

where field like '%foobar jone%'

If there were "foobar jones" and "foobar jonestown" strings, both would be found.

That begs a question. Why is there no syntax to perform the above in a single query? The nature of a quoted expression search is precisely what I need, except for the lack of consistency with regard to the truncate operator. It's very nearly a bug, when you look at it that way.

I think I may try submitting it as such, with appropriate qualifications. Or perhaps even try implementing it myself.