0

I'm just confuse if I will use FTS or plain SQL Query (LIKE). I tried to use FTS on one table but I think it is not accurate when the search value is not a complete word. Here is my table. 

Description, POnumber,PRNumber,InvoiceNumber,CreatedBy,UpdatedBy
Test user          PO1     PR1       Inv 1         userm     userm
Test2              PO2     PR2       Inv 2         userm     userm
Test3              PO3     PR3       Inv 3         userm     userm

SELECT description,ponumber,prnumber,invoicenumber,createdby,updatedby 
FROM MSTR_FILE
WHERE FREETEXT((createdby,updatedby,ponumber,invoicenumber,prnumber,description),'user')

the result is the first row but when the value is 'userm' all the row was selected. My desired result is all the row that contains the userm will be displayed.

Ice_Drop
  • 452
  • 1
  • 9
  • 19

2 Answers2

0

you can use like with OR like this...

SELECT description,ponumber,prnumber,invoicenumber,createdby,updatedby 
FROM MSTR_FILE
where description like '%'+'user'+'%' OR
ponumber like '%'+'user'+'%' OR
prnumber like '%'+'user'+'%' OR
invoicenumber like '%'+'user'+'%' OR
createdby like '%'+'user'+'%' OR
updatedby like '%'+'user'+'%'
Prashant16
  • 1,514
  • 3
  • 18
  • 39
  • I want my search to be efficient, I have read that LIKE cannot use index. http://stackoverflow.com/questions/3213998/performance-of-like-query-vs-full-text-search-contains-query. – Ice_Drop Jan 03 '13 at 00:43
0

I have already found the solution just like @aks suggestion I have tried it but I have included double quotation mark so that it will properly read the "*" sign,

Here is link that enlighten me. http://msdn.microsoft.com/en-us/library/ms187787.aspx

SELECT description,ponumber,prnumber,invoicenumber,createdby,updatedby FROM MSTR_FILE WHERE CONTAINS(createdby,'"user*"');

Ice_Drop
  • 452
  • 1
  • 9
  • 19