469,322 Members | 1,633 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,322 developers. It's quick & easy.

syntax question whole word finding


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.

Jul 23 '05 #1
5 6358
Sri
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

Jul 23 '05 #2
sd********@msn.com (sd********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
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?

Jul 23 '05 #4
sd********@msn.com (sd********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
'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^)

<sd********@msn.com> wrote in message
news:11*********************@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.

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Aaron | last post: by
1 post views Thread by Buddhist[CHinA] | last post: by
1 post views Thread by Michael Yanowitz | last post: by
3 posts views Thread by Tre MoR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.