Changing this
"SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) LIKE lower('%'
|| @p0 || '%')"
to this
"SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) LIKE lower( @p0
)"
works!
````````````````````````````````
On Fri, Nov 29, 2019 at 12:53 AM Kjell Rilbe
[email protected] [firebird-support] <
[email protected]> wrote:
>
>
> Den 2019-11-28 kl. 17:30, skrev [email protected] [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]
>
>
>
