Find words with query

Hi,

I use HF SQL and Querys to find words in (memo) text fields.

If the string is "hello nice world" and the user type "nice world" the query finds the record.
I use a parameter with the "contains" option.

How can I build a query that find 2 or more words in the string, but not in order.

For example: "hello world" should find the record with "hello nice world".


greetings md

Hi md

use wildcart like this:

select field1, field2 from tablename where searchfield like '%hello%world%'

Marc. :xcool:

Hi

First you will need to develop a way to separate the input string into separate words and also determine just how many words you will send to the query.

Using your example of two words......
In the query editor
For the field containing the text to be searched enter 2 parameters (all fields can accept multiple params, just select new a second time) as
MyDataString contains Param1
MyDataString contains Param2

Click on the 'Selection condition' button and select the option and select 'Existing conditions(AND/OR).
The sequence of condition filed will show 1 AND 2, change this to 1 OR 2
WD will validate the change.
Save the changes.
The underlying SQL code will look something like

SELECT
Customer.CustNum AS CustNum,
Customer.CustomerName AS CustomerName,
Customer.Company AS Company
FROM
Customer
WHERE
Customer.CustomerName LIKE %{Param1}%
OR Customer.CustomerName LIKE %{Param2}%

Many variations of selection criteria can be used with this method.

HTH

DerekT

Hi Derek,

yes, I had the same idea now. I create a balloon tooltip in the entry section of the edit field with "Please insert a comma for multiple search words" and fill the parameters with the words.

thx