Subject Re: [firebird-support] How to use a reduced/restricted collation?
Author Dean Harding
phil_hhn wrote:
> What I want to do is search a field, ignoring punctuation, so we could
> do things like
> "... WHERE LASTNAME COLLATE EN_UK = :lastnametosearch ..."
> and then find O'Donnell, even if someone only searched for ODonnell.

The simplest solution, I would think, would be to do it in code. It
depends, of course, on how many fields you want to search in this
manner, but you could just include columns like "last_name" and
"last_name_nopunc" so the "last_name" column would have the "actual"
value and "last_name_nopunc" would be the same, but with punctuation
stripped out.

That way, you can strip out punctuation any way you like, and as long as
you've got the proper indexes, searching will still be fast.

The drawback, of course, would be that you'd have to essentially
duplicate all columns you want to be able to search in this manner.

As another alternative, if you're looking to do "natural" searches, you
might want to use something like Lucene and provide a full-text search.