Hi,

Version: LLBLGen 2.0.0.0 Final, Self-Service DB: MS Access 2003

Is there anyway to use FieldLikePredicate to compare 2 fields? The query I'm trying to generate is as follows:

SELECT s.id, c.categoryname as category
FROM [Categories] c, [Suppliers] s
WHERE c.categoryname LIKE '*' + s.name + '*'

I tried:

IPredicateExpression filter = new PredicateExpression(
                CategoriesFields.CategoryName % ("%" + SuppliersFields.Name + "%"));
                                    

but I don't think it's correct as it gives me an error saying:

Operator '%' cannot be applied to operands of type 'SD.LLBLGen.Pro.ORMSupportClasses.EntityField' and 'SD.LLBLGen.Pro.ORMSupportClasses.Expression'

Thanks in advance for any help!

Hi steve,

The LIKE operator syntax is:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ] 

Where _pattern _represents a fixed string with wildcards.

So you must use **CHARINDEX **(for SQLServer) or **InStr **(for MSAccess)

MSAccess wrote:

InStr Returns a Variant (Long) specifying the position of the first occurrence of one string within another.

Syntax

InStr([start, ]string1, string2[, compare])

So if the function result is greater than 0, the string2 has found in string1!

I tested this and works fine

// define the dbfunction call to compare
DbFunctionCall fnInstr = new DbFunctionCall("InStr", new object[] { CustomersFields.City, CustomersFields.Country});

// use fnInStr at you filter
IPredicateExpression filter = new PredicateExpression();
filter.Add(CustomersFields.Country.SetExpression(fnInstr) > 0);

At your code would look like:

// define the dbfunction call to compare
DbFunctionCall fnInstr = new DbFunctionCall("InStr", new object[] { CategoriesFields.CategoryName, SuppliersFields.Name});

// use fnInStr at you filter
IPredicateExpression filter = new PredicateExpression();
filter.Add(CategoriesFields.CategoryName.SetExpression(fnInstr) > 0);

Thank you~!