query using lots of like %

Posting thread to fulltext to assist and comment
"Hassan" <hassan@.test.com> wrote in message
news:OUfcLsDJIHA.5860@.TK2MSFTNGP04.phx.gbl...
> In addition, do you know how I could create a full text index on it and
> have the query use ? I take it its a chapter by itself, but with the
> simple example below, could you show me how its done ? If not, I'll read
> the chapter(s)
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:e9v5ggDJIHA.2268@.TK2MSFTNGP02.phx.gbl...
>
What version are you using?
Basically, in order to use full-text search (FTS) you need to install it,
enable it in your database, then create one or more full-text catalogs (FTC)
with one or more full-text indexes in them (FTI).
The FTI data can then be accessed using system functions (e.g. CONTAINS,
FREETEXT).
You can find all the help in Books Online, or you can come back to this
newsgroup with a specific question.
For instance, your query could be written like this if you enable FTS on the
affected tables:
select *
from table1 join table2
on table1.col1 = table2.col1
where contains((table1.col2, table1.col3), 'text')
or contains((table2.col2), 'text')
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
|||Matija,
The earlier discussions had some concerns about it not being whole words and
that FTS may not work at best.
Is that true ? Or will it still perform better than what I have today ?
"ML" <ML@.discussions.microsoft.com> wrote in message
news:009BBE19-A77E-4698-B14A-B2BACE5C8653@.microsoft.com...
> What version are you using?
> Basically, in order to use full-text search (FTS) you need to install it,
> enable it in your database, then create one or more full-text catalogs
> (FTC)
> with one or more full-text indexes in them (FTI).
> The FTI data can then be accessed using system functions (e.g. CONTAINS,
> FREETEXT).
> You can find all the help in Books Online, or you can come back to this
> newsgroup with a specific question.
> For instance, your query could be written like this if you enable FTS on
> the
> affected tables:
> select *
> from table1 join table2
> on table1.col1 = table2.col1
> where contains((table1.col2, table1.col3), 'text')
> or contains((table2.col2), 'text')
>
> ML
> --
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
|||Could you give a few examples?
In any case, FTS does not support suffix-searches (*ing - running, walking,
etc.), but prefix-searches are supported (walk* - walked, walking, etc.).
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
|||It will be more *upp* as in support, supported,etc.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:6F893C62-BD00-4FF1-87DA-B922E46D7C15@.microsoft.com...
> Could you give a few examples?
> In any case, FTS does not support suffix-searches (*ing - running,
> walking,
> etc.), but prefix-searches are supported (walk* - walked, walking, etc.).
>
> ML
> --
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
|||In that case FTS most probably won't help you improve the performance.
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
|||If you searched for 'support', and then allowed 'forms of' the word,
'supported' would be included, and FTS could help.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:OiMZtqbJIHA.1208@.TK2MSFTNGP03.phx.gbl...
> It will be more *upp* as in support, supported,etc.
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:6F893C62-BD00-4FF1-87DA-B922E46D7C15@.microsoft.com...
>
|||The more I think about it, we want our users to search for whole words
actually but the problem is these words are searchable in a sentence..
So our query is more like this..
Create table Test
(Col1 varchar(100) ,
Col2 varchar(100),
Col3 varchar(100))
Insert test values( ' I am a big boy', 'I have a big book','whats big? ')
And so our query does a search as following
select * from test
where col1 like '%big%'
or col2 like '%big%'
or col3 like '%big%'
Does that make sense ? Majority of our searches would depend on whole words
but they are buried in a sentence and hence need those wildcards.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uLlrRXiJIHA.1324@.TK2MSFTNGP06.phx.gbl...
> If you searched for 'support', and then allowed 'forms of' the word,
> 'supported' would be included, and FTS could help.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OiMZtqbJIHA.1208@.TK2MSFTNGP03.phx.gbl...
>
|||This is what fulltext search can help with.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:uN8YtSkJIHA.3940@.TK2MSFTNGP05.phx.gbl...
> The more I think about it, we want our users to search for whole words
> actually but the problem is these words are searchable in a sentence..
> So our query is more like this..
> Create table Test
> (Col1 varchar(100) ,
> Col2 varchar(100),
> Col3 varchar(100))
> Insert test values( ' I am a big boy', 'I have a big book','whats big? ')
> And so our query does a search as following
> select * from test
> where col1 like '%big%'
> or col2 like '%big%'
> or col3 like '%big%'
> Does that make sense ? Majority of our searches would depend on whole
> words but they are buried in a sentence and hence need those wildcards.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uLlrRXiJIHA.1324@.TK2MSFTNGP06.phx.gbl...
>
sql