Searching lots of fields

[emozley]

Hi,

I have table that has about twenty fields that I would like to search. Rather than executing a query that says:

SELECT FROM MyTable WHERE FieldA LIKE '%searchtext%' OR FieldB LIKE '%searchtext%' OR FieldC LIKE '%searchtext%' OR FieldD LIKE '%searchtext%' OR FieldE LIKE '%searchtext%' OR FieldB LIKE '%searchtext%' etc..etc...etc..

I was wondering if there is a query that says select everything in row from table where anything in row is like '%searchstring%'. Searching 20 fields with just one search term will be bad enough but if the user enters two, three or even four words into the search box the query will become massive.

Thanks very much

Ed

 

[dhulbert]

You could simply concatonate the fields into one long string then search that

So

Select * from Table where F1 + F2 + F3 like '%Term%'




I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[r937]

if the user enters two, three or even four words into the search box the query will become massive.

i wouldn't call it "massive"


there's no better way to do it, by the way



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ousoonerjoe]

Have you tried the following:

SELECT * 
FROM MyTable
WHERE @SearchText IN (Field1, Field2, Field3);

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[markros]

You can use UNPIVOT technique to search in many fields.

You may find this blog post How to search in all columns in table(s) in the database helpful in explaining the technique.

PluralSight Learning Library

 

[drlex]

You could simply concatonate the fields into one long string then search that

So

Select * from Table where F1 + F2 + F3 like '%Term%'
[/code]

Not so great if there's a NULL in F1.

soi là, soi carré

 

[dhulbert]

Agreed

Should have gone with

isnull(field1,'')+isnull(filed2,'') etc etc



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)