Is there a way to construct a query to select for whole words only?

select id from bookdata where titles like '%Test%' gets everything with
test somewhere in the field. So you could get records which have test
as a subset of a larger word like 'testing' etc...

What I am interested in is finding just the individual words in a
stored field.

Say a field has 'Test your knowledge on this one.' and you want to find
'Test' or 'knowledge' the word, not test or knowledge as parts of
others words etc...anywhere and everywhere in the field and involving
many records . How can you do this? or can this be done?
Thanks for any help.

If I understand what you are saying correctly then this will be the
query:
Select id from bookdata where titles = 'test'. This qry's result will
be only the rows whose title is test.

Let me know if this is what you wanted.
Thanks
Sri

sdowney717@msn.com (sdowney717@msn.com) writes:
> Is there a way to construct a query to select for whole words only?
>
> select id from bookdata where titles like '%Test%' gets everything with
> test somewhere in the field. So you could get records which have test
> as a subset of a larger word like 'testing' etc...
>
> What I am interested in is finding just the individual words in a
> stored field.
>
> Say a field has 'Test your knowledge on this one.' and you want to find
> 'Test' or 'knowledge' the word, not test or knowledge as parts of
> others words etc...anywhere and everywhere in the field and involving
> many records . How can you do this? or can this be done?

To do this reliably in regular SQL is difficult. However, SQL Server
comes with a full-text capability, which I think could be useful here.
I have very little experience with full-text myself, but you can read
about it in Books Online.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Yes, I think that is what I could use.
Apparently SQlserver can make an index of words contained in the fields
Then you search - select name from bookdata where contains(name,'Word
to find')

Anyway you have to build the index before you search it. I am
interested in trying this out.
Does anyone know the syntax for creating a fulltext index?

sdowney717@msn.com (sdowney717@msn.com) writes:
> Yes, I think that is what I could use.
> Apparently SQlserver can make an index of words contained in the fields
> Then you search - select name from bookdata where contains(name,'Word
> to find')
>
> Anyway you have to build the index before you search it. I am
> interested in trying this out.
> Does anyone know the syntax for creating a fulltext index?

As I said, I don't use full-text myself, but I were to look into it,
I would find the relevant sections in Books Online to study. Books Online
is by no means intended for the experts, so why don't you try the same?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

'Test your knowledge on this one.'

try 'Test %' -- NOTE: the *space* before the %
should find 'Test your knowledge on this one.'
but not 'Testing your knowledge on this one.'

try 'Test%' -- NOTE: *no* space before the %
should find 'Test your knowledge on this one.'
and 'Testing your knowledge on this one.'


try '% Test %' -- NOTE: the spaces before and after the %
should find 'Need to Test your knowledge on this one.'
but not 'Test your knowledge on this one.'

where field LIKE '% Test %' OR field LIKE 'Test %' OR field LIKE '% Test'
should catch 'test' anywhere in a string
'test aaa bbb' or 'aaa test bbb' or 'aaa bbb test'

For simple cases this type of stuff can save on the overheads of full text
indexing (and managing the re-indexing of it)

Julian 8^)

<sdowney717@msn.com> wrote in message
news:1110306713.392482.62280@z14g2000cwz.googlegro ups.com...
>
> Is there a way to construct a query to select for whole words only?
>
> select id from bookdata where titles like '%Test%' gets everything with
> test somewhere in the field. So you could get records which have test
> as a subset of a larger word like 'testing' etc...
>
> What I am interested in is finding just the individual words in a
> stored field.
>
> Say a field has 'Test your knowledge on this one.' and you want to find
> 'Test' or 'knowledge' the word, not test or knowledge as parts of
> others words etc...anywhere and everywhere in the field and involving
> many records . How can you do this? or can this be done?
> Thanks for any help.
>