Re: [firebird-support] What key word specifies a search for an entire word?
Den 2019-11-28 kl. 17:30, skrev cte...@gmail.com [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]
Re: [firebird-support] What key word specifies a search for an entire word?
This works:
"SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) LIKE lower( @p0
)"
On Thu, Nov 28, 2019 at 11:09 AM Richard Damon rich...@damon-family.org
[firebird-support] wrote:
>
>
> On 11/28/19 11:30 AM, cte...@gmail.com [firebird-support] wrote:
> >
> > 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"?
>
> if you only want the case that fstName is exactly Rich, then don't use
> like but use equals (=)
>
> If you want Rich as part of the string but not Richard, what is allowed
> before/after Rich that makes it a 'word'?
>
> --
> Richard Damon
>
>
>
Re: [firebird-support] What key word specifies a search for an entire word?
On 11/28/19 11:30 AM, cte...@gmail.com [firebird-support] wrote:
>
> 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"?
if you only want the case that fstName is exactly Rich, then don't use
like but use equals (=)
If you want Rich as part of the string but not Richard, what is allowed
before/after Rich that makes it a 'word'?
--
Richard Damon
Re: [firebird-support] What key word specifies a search for an entire word?
28.11.2019 17:30, cte...@gmail.com [firebird-support] wrote: > What do I modify so it finds only "Rich"? Use SIMILAR TO. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] What key word specifies a search for an entire word?
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"?
