Prev: Calculated Column Syntax Help?
Next: MS SQL Insert Data from One table to another
From: Pat on 12 Jul 2010 16:31
Hi Friends,
SQL 2008 FTS

Why my FTS doesn't return the following data set while using like
does !

Dataset 1:
select * from lo_text lt where lt.title like '%123%'
LEAD0123001002000X820001
CONS8123001001000X820001
Q2 Mandatory Training 2010 (123)

Dataset 2:
select * from lo_text lt where FREETEXT (lt.title,'123')
Q2 Mandatory Training 2010 (123)

I also tried select * from lo_text lt where FREETEXT
(lt.title,'"*123*"'), but still no result

Thanks in advance,
Pat
From: Erland Sommarskog on 12 Jul 2010 16:38
Pat (patrickalexander.email(a)gmail.com) writes:
> Hi Friends,
> SQL 2008 FTS
>
> Why my FTS doesn't return the following data set while using like
> does !
>
> Dataset 1:
> select * from lo_text lt where lt.title like '%123%'
> LEAD0123001002000X820001
> CONS8123001001000X820001
> Q2 Mandatory Training 2010 (123)
>
> Dataset 2:
> select * from lo_text lt where FREETEXT (lt.title,'123')
> Q2 Mandatory Training 2010 (123)
>
> I also tried select * from lo_text lt where FREETEXT
> (lt.title,'"*123*"'), but still no result

Full-text searching can search forms, on initial strings, and on thesarus.
But, no, it cannot search for arbitrary text within a string. For that
task, LIKE is your guy.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Dan on 13 Jul 2010 10:28

"Pat" <patrickalexander.email(a)gmail.com> wrote in message
news:71cea203-4943-4178-a570-77de13708a3b(a)b4g2000pra.googlegroups.com...
> Hi Friends,
> SQL 2008 FTS
>
> Why my FTS doesn't return the following data set while using like
> does !
>
> Dataset 1:
> select * from lo_text lt where lt.title like '%123%'
> LEAD0123001002000X820001
> CONS8123001001000X820001
> Q2 Mandatory Training 2010 (123)
>
> Dataset 2:
> select * from lo_text lt where FREETEXT (lt.title,'123')
> Q2 Mandatory Training 2010 (123)
>
> I also tried select * from lo_text lt where FREETEXT
> (lt.title,'"*123*"'), but still no result
>
> Thanks in advance,
> Pat

FTS is used for searching for words. I'm not sure how the word stemmer
splits up those strings, but it certainly won't store 123 because that's not
a "word" (normally digits are discarded by FTS). You could use FTS to search
for LEAD, or CONS, but nothing else in your values.

You also cannot use * in a FREETEXT (or FREETEXTTABLE) clause because it is
ignored.

You can use * for in a CONTAINS or CONTAINSTABLE clause, but still not in
the way you seem to think it works. You cannot use * at the start of a
string - it's used for prefix matches only, for example ab* will match with
abc and abd, and any other word starting with the letters "ab".

--
Dan

 | 
Pages: 1
Prev: Calculated Column Syntax Help?
Next: MS SQL Insert Data from One table to another