Any way to SQL select string from within a field? 1

[LEICJDN1]

One of my fields is a 255 length text field for comments. I would like to be able to search this text field to see if it contains any specific words.

I have seen the CONTAIN statement used before. Can this be used in ASP?

Is it possible to select a field if it contains a word within a text string or if this not possible in ASP / SQL - any other workarounds?

Thanks.

 

[FesterSXS]

Yes you can use CONTAINS...

strSQL = "SELECT * FROM myTable WHERE Comments CONTAINS('Fred' OR 'Wilma');"

or you can use LIKE and % to perform Wildcard searches...

strSQL = "SELECT * FROM myTable WHERE Comments LIKE 'De%'"

Tony

http://www.SpiraxSarco.com

 

[LEICJDN1]

Thanks for the quick repsonse and example code.

Will go and implement...

Cheers.

 

[LEICJDN1]

Hmm. Can't get it to behave correctly - returns all records regardless of search string entered.

Here is my code:

rs.Source = "SELECT * FROM reftable WHERE comment CONTAINS ('" & searchterm & "')"

searchterm is the string passed from the requesting form.
Have a dummy db with only one entry containing a unique word in the comment field, but this code pulls all the records in the table. Rest of code works fine for simple Where .... = searchterm code.

Any ideas?

PS - What tags do I need to put code in a nice box like yours?

 

[mattygriff]

What values have you tried to search for?

p.s. use

 and

delimiters.

 

[FesterSXS]

[ code ]
your code here
[/ code ]

but without the spaces

Tony

http://www.SpiraxSarco.com

 

[LEICJDN1]

Thanks. Have created a dummy entry with the word 'zippy' int the comments field, then put this directly in the SQL code as follows:

"SELECT * FROM reftable WHERE comment CONTAINS ('zippy');"

Returns all records though. Recall something vague about a SCORE field used with CONTAIN??

 

[FesterSXS]

I'm beginning to think that CONTAINS may be for the Indexing Service.

Have you tried the LIKE method?

Tony

http://www.SpiraxSarco.com

 

[LEICJDN1]

Tony,

Think you might be right about the Indexing Service bit.

Anyway, a little to my suprise, just putting this:

"SELECT * FROM reftable WHERE comment LIKE '%" & searchterm & "%'"

behaves exactly as I want it to.

Thanks very much, have a star for your swift and correct replies.

 

[FesterSXS]

Thanks - glad to see you've learned a new trick

Tony

http://www.SpiraxSarco.com