Easiest way to implement free text search

[Naoise]

tblA
============
fdA | fdB | fdC | fdD

Is there an easy way of searching all the fields in a particular table to see if any of them contain a particular value? I'm trying to avoid having to build a statement like

SELECT * FROM tblA
WHERE fdA LIKE '%MYSTRING%'
OR fdB LIKE '%MYSTRING%'
OR fdC LIKE '%MYSTRING%'
OR fdD LIKE '%MYSTRING%'

as the table schema may change leaving my query outdated.

 

[r937]

several database systems offer fulltext searching

however, they usually involve declaring the colulmns over which the fulltext index pertains

hence with a change in the columns, you'd have to change the index, which is the same predicament you anticipate with the OR LIKE scenario

r937.com | rudy.ca

 

[PHV]

And what about something like this ?
SELECT * FROM tblA
WHERE fdA || fdB || fdC || fdD LIKE '%MYSTRING%'

If any searched column may hold NULL value you may consider using COALESCE.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886