Re: [firebird-support] What key word specifies a search for an entire word?
On 29-11-2019 22:52, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > The fstName is the column title, not the value. I'm using embedded > Firebird. That you're using Firebird Embedded doesn't matter, it works exactly the same as Firebird Server for queries. The problem is that your using two conditions on column fstName using AND. That means for a single row both conditions must be true for that column. The conditions lower(fstName) LIKE lower(@p0) AND lower(fstName) LIKE lower(@p1) will only be true if both @p0 and @p1 can match the same value, which I think is unlikely for a column which contains first names. Maybe you meant OR instead? Mark -- Mark Rotteveel
Re: [firebird-support] What key word specifies a search for an entire word?
The fstName is the column title, not the value. I'm using embedded Firebird. On Fri, Nov 29, 2019 at 3:48 PM Richard Damon rich...@damon-family.org [firebird-support] wrote: > > > On 11/29/19 4:32 PM, Clyde Eisenbeis cte...@gmail.com [firebird-support] > wrote: > > > > Not sure what pattern is. If I am searching for two words I use OR or > > AND. > > > > "SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) LIKE lower( > > @p0 ) AND lower(fstName) LIKE lower( @p1 ) " > > > > I prefer the word LIKE. Is easier to avoid errors vs. using '='. Are > > there advantages to using '='? > > > LIKE, because it is designed as a partial match, can't use a normal > index, so often ends up doing a table scan. > > = since it only matches the full field, can easily use an index, and if > an appropriate index is available, can avoid scanning the whole table. > > Also, in your above sample, fstName needs to match BOTH p0 and p1, which > isn't usual unless you are looking for the presence of both of two > strings in any order. > > -- > Richard Damon > > >
Re: [firebird-support] What key word specifies a search for an entire word?
On 11/29/19 4:32 PM, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > > Not sure what pattern is. If I am searching for two words I use OR or > AND. > > "SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) LIKE lower( > @p0 ) AND lower(fstName) LIKE lower( @p1 ) " > > I prefer the word LIKE. Is easier to avoid errors vs. using '='. Are > there advantages to using '='? > LIKE, because it is designed as a partial match, can't use a normal index, so often ends up doing a table scan. = since it only matches the full field, can easily use an index, and if an appropriate index is available, can avoid scanning the whole table. Also, in your above sample, fstName needs to match BOTH p0 and p1, which isn't usual unless you are looking for the presence of both of two strings in any order. -- Richard Damon
Re: [firebird-support] What key word specifies a search for an entire word?
Not sure what pattern is. If I am searching for two words I use OR or AND. "SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) LIKE lower( @p0 ) AND lower(fstName) LIKE lower( @p1 ) " I prefer the word LIKE. Is easier to avoid errors vs. using '='. Are there advantages to using '='? ``` On Fri, Nov 29, 2019 at 12:40 PM Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] < firebird-support@yahoogroups.com> wrote: > > > Do you use pattern or simple string? > > If it is simple string then better is using „=” instead of like. > > > > "SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) = lower( @p0 )" > > > > Regards, > > Karol Bieniaszewski > > >
ODP: [firebird-support] What key word specifies a search for an entire word?
Do you use pattern or simple string? If it is simple string then better is using „=” instead of like. "SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) = lower( @p0 )" Regards, Karol Bieniaszewski
Re: [firebird-support] What key word specifies a search for an entire word?
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
kjell.ri...@marknadsinformation.se [firebird-support] <
firebird-support@yahoogroups.com> wrote:
>
>
> 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]
>
>
>
