Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VFP "Contains" syntax 2

Status
Not open for further replies.

Brian_Calnan

Technical User
Oct 5, 2023
2
IE
Hi,

I have a VFP table which has a field named SD_CODE. Some of the records on the table contain the word "RAP" in SD_CODE. I want to filter for the records that contain "RAP".

I tried this:

SET FILTER TO SD_CODE CONTAIN "RAP"

but I'm getting an "unrecognized phrase/keyword" error.

Can anyone tell me the correct syntax?
 
You're welcome. You an use
Code:
Set filter to AT("RAP",SD_CODE)>0
.
And if you use ATC() instead of AT(), case will be ignored.
 
Well, and for sake of completeness, the SQL clause VFP also knows for this is LIKE. Indeed CONTAINS as in T-SQL or Oracle SQL (for example is not available in VFP, as you also don't have the fulltext indexing and searching domain of the SQL language in VFP SQL.

But LIKE works, as in...
Code:
...Where SD_Code Like '%RAP%'
would find any SD_Code with "RAP" inside.

VFP also has the LIKE() and LIKEC() functions to do the same outside of SQL:
Code:
Set Filter To LIKEC('*RAP*',SD_Code)

They differ from the LIKE clause in being a function and - making it unnecessarily unintuitive if you ask me - putting the pattern as the first parameter and not using the SQL placeholder character % but *, as known from file name patterns like '*.*' (for any file with any file extension).

VFP also has some other functions to mimic clauses and operations known from SQL outside SQL, too. The BETWEEN() function can do about the same as an SQL b BETWEEN a and c. Instead you write BETWEEN(b,a,c) and the INLIST function can do about the same as SQL's a in (a,b,c) with INLIST(a, a,b,c). But not in SQL, please, VFP SQL also knows the BETWEEN and IN clauses and it therefore is backwards to use them within SQL.

Anyway, $ also puts what you look for first and you can best remember it when you read it as "is contained in" operator, so "RAP" $ SD_CODE reads "RAP" is_contained_in SD_CODE.

Chriss
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top