SQL question

[Peter Haworth]

On Wed, Sep 19, 2012 at 3:07 AM, Klaus on-rev <klaus at major.on-rev.com>wrote:

> Hi Peter,
>
> Am 19.09.2012 um 07:40 schrieb Peter Haworth <pete at lcsql.com>:
>
> > I'm not sure that will work.  If Column2 started with "MyCriteria", I
> don't
> > think it would be selected.  Any queries of the form LIKE '%mycriteria%'
> > would work though.
>
> using wildcards is not my problem ;-)
>

I understand, but if you end up using any of the solutions that
concatenates columns together, you'll have to use LIKE '%xyz%' instead of
='xyz'.

>
> > Klaus, have you implemented a solution to this yet?  I don't recall you
> > mentioning which flavor of SQL you're using and that makes a difference.
>
> ah, sorry, I will use a local SQLite database.
>

OK, good to know.  I don't remember all the solutions you've been given,
but here's a couple of notes.  Apologies if you already know this.

If you concatenate columns together, the operator to do that in sqlite is
|| not +.

I think there was one solution that used PATINDEX - sqlite doesn't have
that function.

I implemented exactly the same functionality in a name/address search db
once and ended up biting the bullet and using a WHERE clauses that tested
every column in the table for the string I was looking for.  A pain to code
because I probably had 20 or 30 columns to search but sometimes the
simplest solution is the best :-)

>
> > Pete
> > lcSQL Software <http://www.lcsql.com>
> >
> >
> > On Tue, Sep 18, 2012 at 8:13 PM, Mark Stuart <mfstuart at cox.net> wrote:
> >
> >> Klaus try concatenating the columns like this:
> >> SELECT Column1, Column2, Column3 FROM Table WHERE (Column1 + Column2
> LIKE
> >> 'MyCriteria%')
>
> Hi Mark,
>
> sounds good, will try this.
>
> I want to provide a "Search" field where the user can use a popup to
> select a specific "database field" or a "global" search.
>
>
> Thanks for all the hints, so far, I'm sure I will use one of them :-)
>
>
> Best
>
> Klaus
>
> --
> Klaus Major
> http://www.major-k.de
> klaus at major.on-rev.com
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>