Subject Re: [firebird-support] SQL Puzzle: Two Way String Matching
Author unordained
---------- Original Message -----------
From: "red_october2009" <kevin.wendy.morris@...>
> I need an SQL statement that will return all records where at least
> one human name is found in the pet names field (not the other way
> around). In this case the only records that qualify are:
------- End of Original Message -------

create procedure split_list (incoming varchar(32000), delimeter varchar(5))
returns (
outgoing varchar(32000)
) as
/* iterate through incoming, using substring(), pos(), and suspend to return
each chunk individually; also, I suggest you trim() the results and convert them
to a well-defined case (upper or lower) */

select, real_names, pet_names
from people
where exists (select * from split_list(pet_names) as pet_split where
pet_split.outgoing in (select real_split.outgoing from split_list(real_names) as

You could do some stuff with joining (left) to the SP, but I think that's nastier.
Untested, and I'm feeling rather under the weather, but generally that's the
direction I'd go. And then you can use that same SP that you build to also
normalize your data as Norman suggests.