fulltext index

[yebaws]

I've come up against another problem using searches of fulltext indexes and hope somebody can offer me a bit of advice....

One column has a CSV list of 2, 3 & 4 character initials - for example: "BL, BT, GL, IWM, NMM, NRA, TNA,". I want to be able to search for rows that contain ANY particular initials.

For example, I can build a string "BL, GL, TNA" with check boxes to search for all rows containing any of those initials. But mysql only indexes words of 4 or more characters by default. I've emailed my ISP to see if they can change the configuration to allow indexing of 2 character words, but I'm guessing that the answer will be "no" as it will probably require a server wide change. So is there a way around this?

Using LIKE won't work because a LIKE search for "BL, BT, GL, IWM, NMM, NRA, TNA," will only return a row that has exactly that string, and not one that just contains BL for example. I'm a bit stumped with this one.....

[bazz]

post your query so far and we may be able to help.


bazz

[guelphdad]

Normalize your data instead.

[bazz]

Oh, I missed that bit where he said

One column has a CSV list of 2, 3 & 4 character initials ....

[yebaws]

Originally Posted by guelphdad

Normalize your data instead.

well, yes, in an ideal world. And I'll do that in the long term. I didn't make the database, so I'm not guilty there. But I need this search up quickly and normalising the data will not be quick. Should have said that to start with, anticipating the responses....

[guelphdad]

use find_in_set

[yebaws]

Originally Posted by guelphdad

use find_in_set

thank you - that's the one!