On Fri, Nov 29, 2019 at 12:53 AM Kjell Rilbe kjell.rilbe@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

Den 2019-11-28 kl. 17:30, skrev cte677@... [firebird-support]:
> I have this:
>
> "SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) LIKE
> lower('%' || @p0 || '%')"
>
> Assume fstName includes "Richard" and "Rich".  If I search for "Rich"
> it finds both names.
>
> What do I modify so it finds only "Rich"?


I usually solve that kind of problem like this:

SELECT fstName, fiKeyID FROM Members WHERE ' ' || lower(fstName) || ' '
LIKE lower('% ' || @p0 || ' %')

Note that I add a leading and a trailing space to the searched string,
and also to the sought string (inside the % wildcards). This will ensure
that it finds the sought string only if it's surrounded by spaces, and
will also find it at the beginning and at the end of the searched
string, since we added spaces there.

The downside is that no index can be used for this search, but that's
probably true with other approaches too.

An alternative could be:

SELECT fstName, fiKeyID FROM Members

WHERE lower(fstName) LIKE lower(@p0 || ' %') -- At the start? Note space
before %
  OR lower(fstName) LIKE lower('% ' || @p0) -- At the end? Note space
after %
  OR lower(fstName) LIKE lower('% ' || @p0 || ' %') -- In the middle?
Note spaces inside %

Regards,
Kjell


[Non-text portions of this message have been removed]