Search for full words in text field

Applications Grid / Reports
#1

Dear All,

I need to be able to search a text field using the Contains option in the full search where I can specify the various fields to be searched. But the Contains option returns any string matching the input value. I only want it to find instances where the whole word is found. For example, you have a text field such as “This sentence contains the word woman.” But, you are looking for “This sentence contains the word man.” The search would return both of these sentences because ‘man’ is included in ‘woman’. But if it were to execute a search for full words, then only the sentence with ‘man’ would be returned.

Is there a way to input a search or configure a search for whole words using Contains or some other approach?

Hope I stated this clearly.

Thanks so much for any suggestions!!!

Lyle

#2

Try:

SELECT *
FROM table
WHERE keywords REGEXP ‘[[:<:]]man[[:>:]]’

#3

Hi Albert,

Thank you for your response as always. Is there any way to incorporate this into the Search function on a browse? ie Regular expressions in a Search field?

Thanks so much!

Lyle

#4

Hi Albert,

I created a search application and linked it to a form application and it works nice. I’m thinking that this was what you thinking I was doing. So now I’m trying to figure out how to apply the sql regular expression. For example, I would like for the user to be able to input a search term (like ‘man’) as we were discussing, and then the search would insert the term and your sql expression would be run and filter the form.

I hope that makes sense.

I don’t know how to add a variable to the search form or how to get it to work with a sql filter like discussed above.

Any thoughts much appreciated.

Thanks again!!!

Lyle

#5

I tend to be short in my answers sometimes :wink: But I must be honest I don’t know how to change the search expression. What you might be able to do is to create a small control app containing your search items, then create your sql argument in a global var and redir to your form using the global sql. The regexp I found on the internet search so you might need to find out if it works on your database. AFAIK it works on MySQL, but don’t know if it on others.

#6

Hi Albert,

Okay, I’ll give that a shot. Thanks again for your help. Much appreciated.

Best,

Lyle