• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

General SQL Query Question

How does one pass a LIKE query to all the fields in a database?  For example, the following:
     SELECT * from Genetics WHERE Genetics.author LIKE '%smith%'
Returns records with "smith" in the authors field, but what if I wanted to search for "smith" in ALL the fields?
I tried
     SELECT * from Genetics WHERE Genetics.* LIKE '%smith%'
And it didn't work (yes, I'm new to SQL).

Any help would be appreciated.

Thanks,

Dave Stephens

0
stepherd
Asked:
stepherd
1 Solution
 
tschill120198Commented:
I would be surprised if there was a way to do it with just a single line of code...  it could be done using UNION.

Suppose the Genetics table had columns "author", "title", "description".  You could use something like the following:

select * Genetics where author like '%smith%'
union
select * Genetics where title like '%smith%'
union
select * Genetics where description like '%smith%'

If you don't know in advance what the columns are you could build the SQL dynamically.


0
 
stepherdAuthor Commented:
Thanks for the input. Yeah, I thought this might be the case.  I am building the SQL dynamically using PERL, so it shouldn't be a problem to approach it as you suggest.
Never hurts to ask! Thanks.

--Dave Stephens

0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now