SQL question

[Bob Sneidar]

Peters way seems like it would be quicker, although more work to implement, because it keeps an index of the columns you want to search for up to date. For a large table this would be the way to go. For a smaller dataset, you could probably get away with Mike's solution of using the concat() function and a wild card. Keep in mind that if any of the columns is binary or numeric, unless you are using mySQL 5.5.3 or better, you will have to use the cast function. Or so I read. 

Bob


On Sep 17, 2012, at 9:41 AM, Peter Haworth wrote:

> 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
>> 
> _______________________________________________
> 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