SetQueryConditionLike ( _datafield ; _condition ; _orchar ; _andchar ; _leadingWC )

Generate SQL WHERE-conditions for a string of (multiple) value(s)

Be the first to rate this function Log in to vote

Otmar Kramis - Show more from this author
https://developer.balimusic.ch

Sample input:

SetQueryConditionLike ( "FullName" ; "John,James&Jeffry" ; "" ; "" ; 1 )

Sample output:

(UPPER(FullName) LIKE '%JOHN%' OR UPPER(FullName) LIKE '%JAMES%' AND UPPER(FullName) LIKE '%JEFFRY%')

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/**********************************************************************************

SetQueryConditionLike ( _datafield ; _condition ; _orchar ; _andchar ; _leadingWC )

Author : Otmar Kramis

Parameter :
_datafield : The field to query
_condition : The search value(s)
_orchar : the character working as OR (default ",")
_andchar : the character working as AND (default "&")
_leadingWC : 1 if search term should start with %

Asterix "*" is converted to "%"

Example call :
SetQueryConditionLike ( "FullName" ; "John,James&Jeffry" ; "" ; "" ; 1 )

Result :
(UPPER(FullName) LIKE '%JOHN%' OR UPPER(FullName) LIKE '%JAMES%' AND UPPER(FullName) LIKE '%JEFFRY%')

***********************************************************************************/

Let ( [

_orchar = If ( _orchar = "" ; "," ; _orchar )
; _andchar = If ( _andchar = "" ; "&" ; _andchar )
; _result = Substitute ( 
If ( _leadingWC ; "%" ) & Upper ( _condition ) 
; [", " ; ","] 
; ["& " ; "&"] 
; [_orchar ; "%' OR UPPER(" & _datafield & ") LIKE '" & If ( _leadingWC ; "%" )] 
; [_andchar ; "%' AND UPPER(" & _datafield & ") LIKE '" & If ( _leadingWC ; "%" )] 
) 

; _result = Substitute (
_result
; ["*" ; "%"] 
; ["%%" ; "%"] 
) & "%')"

; _result = Substitute (
"(UPPER(" & _datafield & ") LIKE '" & _result
; " OR UPPER(" & _datafield & ") LIKE '%'" ; "" 
)

] ;

_result

)

Builds a WHERE-condition LIKE string for a SQL field and a search string containing search values delimited by OR-character or/and AND-character. Parameter 3-5 can be empty, defaulting to "," for OR, "&" for AND and no leading wildcard (%). The asterix "*" is handled as SQL wildcard.

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

SetQueryConditionLike ( _datafield ; _condition ; _orchar ; _andchar ; _leadingWC )

Comments

Support this website.

Support this website.