Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

Re: DBI - Multi-field SQL: *OR* condition w/o specifying field names...possible?

by Arguile (Hermit)
on Oct 05, 2002 at 04:39 UTC ( #202997=note: print w/replies, xml ) Need Help??

in reply to DBI - Multi-field SQL: *OR* condition w/o specifying field names...possible?

It's possible. I've never deeply explored SQL::Statement or AnyData, but a generic approach using straight DBI is easy.

Grab the field names of the table (using either DBI's not-completely-supported-by-all-DBMSs metadata functions or a DBMS specific query). Using that list dynamically construct the ‘OR’ section of the WHERE clause.

While that method would do what you're asking for, it's not all that useful a query and quickly becomes very very expensive. The prefered approach for such a query is to use a full text search engine, which is generally DBMS dependent.

A good FTS will allow you to give weightings to fields (such as finding ‘foo’ in the title meaning more than just finding it in the body), search on multiple terms with the option of giving higher weightings when terms are close together, stemming, etc. All with a reasonable cost (I'm talking machine resources).

Many popular RDMBS packages have FTS engines either included or as additions. Oracle has many included tools as well many third party options, MS SQL Server has some FTS included IIRC, MySQL has rather limited FTS capability, and PostgreSQL has a few options (check under contrib/ or OpenFTS which is actually written largely in Perl). DBMSs’ that aren't listed here probably have such options, I just can't remeber or don't know them well enough.

Sorry for the brevity of the reply, but it's late and I don't have time to go into the topic deeply. Hope it helps anyways.

  • Comment on Re: DBI - Multi-field SQL: *OR* condition w/o specifying field names...possible?

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://202997]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (4)
As of 2018-06-24 21:18 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (126 votes). Check out past polls.