SQL server search

I'm going to perform a search in my SQL server DB (ASP.NET, VS2010,C#), user types a phrase and I should search this phrase in several fields, how is it p开发者_运维百科ossible? do we have functions such as CONTAINS() in SQL server? can I perform my search using normal queries or I should work in my queries using C# functions?

for instance I have 3 fields in my table which can contain user search phrase, is it OK to write following sql command? (for instance user search phrase is GAME)

select * from myTable where columnA='GAME' or columnB='GAME' or columnC='GAME

I have used AND between different conditions, but can I use OR? how can I search inside my table fields? if one of my fields contains the phrase GAME, how can I find it? columnA='GAME' finds only those fields that are exactly 'GAME', is it right?

I'm a bit confused about my search approach, please help me, thanks guys

---

OR works fine if you want at least one of the conditions to be true.

If you want to search inside your text strings you can use LIKE

select * from myTable where columnA like '%GAME%' or columnB like '%GAME%' or columnC like '%GAME%'

Note that % is the wildcard.
If you want to find everything that begins with 'GAME' you type LIKE 'GAME%', if you allow 'GAME' to be in the middle you need % in both ends.

---

You can use LIKE instead of equals and then it can contain wildcard characters, so your example could be:

select * from myTable where columnA LIKE '%GAME%' or columnB LIKE '%GAME%' or columnC LIKE '%GAME%'

Further information may be found in MSDN

This is going to do some pretty heavy lifting in terms of what the database has to do though - I would suggest you consider something like full text search as I think it would more likely be suited to your scenario and provide faster results (of course, if you never have many records to search LIKE would probably do fine). Information on this is also in MSDN

---

Don't use LIKE, as suggested by other answers. It won't work with indexes, and therefore will be slow to return and expensive to run. Instead, you have two options:

Option 1: Full-Text Indexes

do we have functions such as CONTAINS() in SQL server?

Yes! You can use the CONTAINS() function in sql server. You just have to set up a full-text index for each of the columns you need to search on.

Option 2: Lucene.Net

Lucene.Net is a popular client-side library for searching text data that integrates closely with Sql Server. You can use it to make implementing your search a little easier.