Searching text fields without full text search? - Russell Fields

LIKE works strictly in string order so:

SELECT * FROM ATable WHERE TextColumn LIKE '%hotel%in%europe%'

This returns only those rows where those three strings are in that order,
but not '.... europe, hotel in....'

SELECT * FROM ATable WHERE TextColumn LIKE '%hotel_in_europe%'

This returns only those rows where the string of words with one unidentified
character exists between each, but would not return '.... hotel. In Europe
....' because there are two characters between 'hotel' and 'in'.

Et cetera.

RLF

Searching text fields without full text search? - M

In addition to what Russel said in his post, LIKE will force a scan (with the
exception of prefix searches - 'hotel%'), while on the other hand FTS is
comparatively more optimized due to the way FTI data is stored (and more
flexible search-wise).


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Searching text fields without full text search? - PSiegman

How flexible is "like" ?

Can it only handle very simple stuff, like, searching for exact one
word, or, can I use it for something more, like using for two or more
words? ("hotel+in+europe")

If so, how?

Searching text fields without full text search? - PSiegman

One question: Where is the data of FTS stored? (The index etc.)


Some clients are using SQL Express 2005, and that one has the 4 GB
limit... does the FTS data add to the limit of a database?

Searching text fields without full text search? - Russell Fields

The FTS data is stored in a catalog on the file system.  This is outside the
mdf and ldf, but is backed up along with them when you do a backup.

4GB is the max size of the mdf and does not include the ldf (the transaction
log), so I would be very, very surprised if the Full Text data were counted
against the 4GB.  But you will also need to download and install the
Advanced Services for SQL Server Express to get the FTS.

(Thanks ML, for emphasizing the slowness of totally wild card LIKE
statements.)

RLF

Searching text fields without full text search? - M

I've looked through Books Online, but couldn't find any reference that would
suggest that FTS data counts into the 4 GB limit for database size in SQL
Server 2005 Express.

Russel,
Thank you for introducing the term "totally wild card" to me. :)


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Searching text fields without full text search? - Russell Fields

RLF

Searching text fields without full text search? - M

Actually, I think that one's called "evenly wild card". :)


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/