>>>SQL select help says "The Where clause supports the ESCAPE operator for the JoinCondition, allowing you to perform meaningful queries on data containing the SELECT - SQL % and _ wildcard characters."
>>>
>>>Has anyone used this or got it to work, and if so, can you provide proper syntax? Since help doesn't mention what the 'Escape operator' is, I tried the literal WHERE ESCAPE '%' but I failed to perform any 'meaningful queries';)
>>
>>From the SELECT - SQL help:
>>
>>
Example 9>>
>>Example 9 displays FilterCondition in the form of FieldName [NOT] LIKE cExpression
>>
>>customer.country NOT LIKE "UK"
>>>/pr>
>>This filter condition searches for each field that matches cExpression.
>>
>>You can use the percent sign (%) and underscore ( _ ) wildcards as part of cExpression. The percent sign represents any sequence of unknown characters in the string. An underscore represents a single unknown character in the string.
>
>It was the mention of 'Escape operator' in the help which didn't make any sense (I still don't understand what that means).
>
>I was actually looking for a way to construct a WHERE clause which allowed me to specify a wildcard in such a way that I could allow a user to specify a specific condition or if they didn't, still get all the records. The following solution worked:
>
>test = "%"
>SELE
> WHERE Cfevents.ev_eventdesc LIKE ?test
Sylvia,
ESCAPE clause is maybe the most rare one used in SQL. Your data might contain % or ? which are wildcard chars for SQL. ie:myfield
-------
Hello
My %0.1 worth idea
...
You want to SQL select those records that contain a % sign.select * from mytable where myfield like "%!%%" ESCAPE "!"
Here "!" is used as an ESCAPE char to denote search results would include literal %. To search for all fields conatining literal "%12" it would be :select * from mytable where myfield like "%!%12%" ESCAPE "!"
Otherwise "My 12", "12 brothers" would also fit. If you ever gonna use ESCAPE char too then precede it with itself. See examples 11,12,13 in SQL select examples.
Now for your case :select whatever from whereever ;
where myfield like "%"+alltrim(thisform.sometextbox.value)+"%"
Works for char data. Beware it is case sensitive. And if you're not looking for "contains" (just starts with including "" for all) thenselect whatever from whereever ;
where myfield = alltrim(thisform.sometextbox.value)
is faster.
Cetin
