How to search a word in all column of table?

Question

I have a View that I like to search a word in it.
How can I search a word in all column of table?

SELECT *
  FROM [Vw_FullTechInformation]

http://msdn.microsoft.com/en-us/library/ms142571.aspx – Mihai Jun 21 '14 at 11:22 — Mihai, Jun 21 '14 at 11:22

DiDi · Answer 1 · 2014-06-21T17:18:34.593

1

You can do full-text search on all columns like this

-- using contains
SELECT *
  FROM [Vw_FullTechInformation]
  WHERE CONTAINS(*, 'YourSearchText');
--using freetext

SELECT *
  FROM [Vw_FullTechInformation]
  WHERE FREETEXT(*, 'YourSearchText');
--using freetexttable
  SELECT *
  FROM FREETEXTTABLE([Vw_FullTechInformation],*,'YourSearchText')

But your table/view should be full-text indexed for this to work.

edited Jun 21 '14 at 17:18

answered Jun 21 '14 at 16:26

DiDi

110
6

This might help you on implementing full-text search on view [Implementing Full-Text Search on View](http://www.dotnetfunda.com/articles/show/1019/implementing-full-text-search-on-view) And [Enable Full-Text Search on View with Inner Join](http://stackoverflow.com/questions/8486703/enable-full-text-search-on-view-with-inner-join) – DiDi Jun 21 '14 at 20:00
My View has `OUTER JOIN` so I couldn't create cluster Index for Full-Text search. – user3713495 Jun 27 '14 at 03:24
Then may be this is your question, [Using Full-Text Search in SQL Server 2008 across multiple tables, columns](http://stackoverflow.com/questions/403630/using-full-text-search-in-sql-server-2008-across-multiple-tables-columns) and the solution [Indexed Views With Outer Joins](http://www.sqlservercentral.com/articles/Indexing/indexedviewswithouterjoins/1884/) – DiDi Jun 27 '14 at 19:41

score 0 · Answer 2 · answered Jun 23 '14 at 06:19

Try,

firsttable Record Like this

ID  Value
1   First
2   Second
3   Third
4   FOUR
5   FIVE
6   six

SELECT *  FROM firsttable 
WHERE Value LIKE '%s'           
   OR Value LIKE 's%'        
   OR Value LIKE '%s'        
   OR Value LIKE '%s%' ; 

**Output Like**

ID  Value
1   First
2   Second
6   six

How to search a word in all column of table?

2 Answers2