SQL question

[Peter Haworth]

Hi Klaus,
Don't think you cand o that with standard SQL tables. You can do it with
SQLite Full TExt Search tables

It could also be done with standard tables as follows.

Set up an index table with columns named table, text, and key.  In your
example, every time your Persons table is updated, you would also maintain
the Index table with the contents of each column in Persons that you wanted
to search on.  Then your wildcard searches would be on the Index table with
a join statement to Persons.  You wouldn't need the Table column if the
onbly table you wanted to index like this was Persons.

For example, let's say you had Persons entries with San Diego (Persons
Primary Key1), San Francisco (Persons Primary Key 2) in the City column and
Sarah Brown (Persons Primary Key 1), Sally Green (Persons Primary Key 3) in
the name column.  Your index table would end up with the following entries
(The table column would always contain "Persons")

KEY  TEXT
1        San Diego
1        Sarah Brown
2        San Francisco
3        Sally Green

Your SQL SELECT would be:

SELECT * FROM Index WHERE Table='Persons AND Text LIKE 'sa%' JOIN Persons
on Person.<PrimaryKeyname>=Index.Key

If you are using SQLite, your could use Triggers to keep the Index Table up
to date with any inserts, updates, or deletes from Persons so the SQL in
your program would not have to deal with it.  I can give you more info on
that if you need it.

Pete


Pete
lcSQL Software <http://www.lcsql.com>



On Mon, Sep 17, 2012 at 8:59 AM, Klaus on-rev <klaus at major.on-rev.com>wrote:

> Hi friends,
>
> I know that we can use WILDCARDS in database searches with "LIKE"
> ...
> SELECT * FROM Persons WHERE City LIKE 'sa%'
> ...
>
> I am wondering if it is possible to search the complete table instead of a
> particular column.
> Know what I mean? Like this pseudocode:
> ...
> SELECT * FROM Persons WHERE ANY_COLUMN LIKE 'sa%'
> ...
>
> Thanks for any hints or workarounds if this is not possible ;-)
>
>
> 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
>