VFP "Contains" syntax 2

[Brian_Calnan]

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?

 

[Tore Bleken]

SET FILTER TO "RAP"$SD_CODE

 

[Brian_Calnan]

That worked. Thank you!

 

[Tore Bleken]

You're welcome. You an use

Set filter to AT("RAP",SD_CODE)>0

.
And if you use ATC() instead of AT(), case will be ignored.

 

[Chris Miller]

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

https://learn.microsoft.com/de-de/sql/t-sql/queries/contains-transact-sql?view=sql-server-ver16)

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...

...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:

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