How to search multiple columns, in 1 or more tables, for a single string?

Q: How can I search all varchar columns in a given table for a specific string, without building the where clause for every field?

A: select * from table1 where val1 + val2 like '%xyz%'
/* where val1 and val2 are varchar columns */

Also, if you have full text indexes defined on the columns of a table you can use the contains clause to examine each column that is indexed. If all of the varchar columns in mytable are are included in the fulltext index, then following will return any row that has the string abc :

select * from mytable where contains(*, '"*abc*" )

Thanks to Pete F. for helping with this question.


Popular posts from this blog

Working with double quotes (") in VB.Net

Using VB Script to display part of an IP address

Object reference not set to an instance of an object when using HTMLEditorExtender