hey all,

i have 3 fields in a table that are of interest for searching.
Which would be better to do or faster or efficient?
concat all three fields and perform my LIKE search over that field
or
saying (field1 like 'this') or (field2 like 'this') or (field3 like 'this')

thanks,
rodchar

Concat? And then?

field1+field2+field3 like 'this%this%this%'?

This won't use an index.

This can use an index, as far as I know.

Are you doing a where Field1+ Field2+ Field3 like '%abc %'
or
are you concatinating the fields in the DB

also are they searching like 'abc%' or like '%abc%'
'abc%' is much faster because it can use the index

http://sqlservercode.blogspot.com/



Show quoteHide quote

i was thinking about doing in the db so my application can just search 1
field but is that worth the trouble?

Show quoteHide quote

One option is to use :

WHERE @param IN ( col1, col2, col3 ) ;

That would only work for exact matches not for part of a string

http://sqlservercode.blogspot.com/


Show quoteHide quote

Ah, I missed the LIKE part. Another way to do this would be to derive the
data from multiple columns using a UNION, for instance, to search three
columns :

SELECT *
  FROM tbl t1
WHERE EXISTS ( SELECT *
                  FROM tbl t2,
                       ( SELECT 1 UNION
                         SELECT 2 UNION
                         SELECT 3 ) D ( n )
                 WHERE t1.key_col = t2.key_col
                   AND CASE n WHEN 1 THEN searched_col1
                              WHEN 2 THEN searched_col2
                              WHEN 3 THEN searched_col3
                       END LIKE '%' + @param + '%' );

True, but technically the OP did not use a syntax that would look at part of
the column value - they did not include leading or trailing '%' characters.
So based on the OP's post, Anith's suggestion is a valid one.

Show quoteHide quote

Torn over a normalizing decision
Mulitple Create Views in Query Batch
Strange Timeout problem.
Book Recommendations
length of text in row in table