ContainsExact ( Text ; SearchStrings )

checks whether at least one of the values in SearchValues is an exact match for a portion of Text

Average rating: 3.5 (2 votes) Log in to vote

Bill Thurmes - Show more from this author
MDCA
http://www.miyotadca.com

Sample input:

ContainsExact ( "CA" ; "GA;FL;MN;CA" )
ContainsExact ( "LeBron James" ; "Lebron" )
ContainsExact ( "Jim,Jane" ; "m\,J" )

Sample output:

1
0 (not the same capitalization)
1

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

/*******************************
* Bill Thurmes 2017
* Syntax: ContainsExact ( Text ; SearchStrings )
* Text = any text, including a delimited value list
* SearchStrings = Delimited value list, with accepted delimiters being commas, semicolons, or ¶
* To include commas, semicolons, ¶ (to be matched) in SearchStrings, include \ before them
*
* Purpose: Determine whether Text contains any of the strings contained in SearchStrings ('or' logic)
*   and that those strings are exact matches (useful when ESQL queries depend on the function)
* Similar to Position function but will assess several SearchStrings, gives 0 or 1 (No or Yes) as Result
* Restriction: the following !-containing strings may not be part of SearchStings or Text: !c! !s! !p! ~`_~`_~`_
*
* Examples: ContainsExact ( "24128" ; "412" ) [= Contains ( 24128 ; 412 )]; Result = 1
* ContainsExact ( "Jim Charlie Frank Jane Michelle George Kim Chris" ; "Julie,Melinda,Rachel" ) = 0
* ContainsExact ( "CA¶CO¶DE¶IA¶MN¶ND¶SD" ; "GA¶FL" ) = 0
* ContainsExact ( "CA" ; "GA;FL;MN;CA" ) = 1
* ContainsExact ( "CA" ; "GA;FL;MN;Ca" ) = 0 Match but not exact
* ContainsExact ( "Jim,Jane" ; "m\,J" ) = 1
********************************/

Let ( [ 
Text = Substitute ( Text ; ¶ ; "!p!" ) ;
SearchStrings = Substitute ( 
		SearchStrings ; 
			["\," ; "!c!" ] ; 
			["\;" ; "!s!" ] ; 
			["\¶" ; "!p!" ] ; 
			[ "," ; ¶ ]; 
			[ ";" ; ¶ ] 
	) ;
interim = Case ( 
		ValueCount (SearchStrings ) > 1 ; /*IF*/
		/*THEN*/
		If ( /*used to convert PatternCount to 1 or 0*/
			PatternCount ( 	/*use this to count nonsense string*/
				Substitute ( /*use this to exactly match the value from SearchStrings...*/
							Text ; 
							Substitute ( 
								GetValue ( SearchStrings ; 1 ) ; 
								[ "!c!" ; "," ] ; 
								[ "!s!" ; ";" ] 
								) ;
							"~`_~`_~`_"		/*... and replace it with nonsense string*/
							) ;
				"~`_~`_~`_"
				) ;
			1 ;
			0 ) /*End If*/

			&
		ContainsExact ( /*note: if CF name is changed, change here too*/
				Text ; 
				RightValues ( 
					SearchStrings ; 
					ValueCount ( SearchStrings ) - 1 ) 
				) ;
			
		If ( /*used to convert PatternCount to 1 or 0*/
			PatternCount ( 	/*use this to count nonsense string*/
				Substitute ( /*use this to exactly match the value from SearchStrings...*/
							Text ; 
							Substitute ( 
								GetValue ( SearchStrings ; 1 ) ; 
								[ "!c!" ; "," ] ; 
								[ "!s!" ; ";" ] 
								) ;
							"~`_~`_~`_"		/*... and replace it with nonsense string*/
							) ;
				"~`_~`_~`_"
				) ;
			1 ;
			0 ) /*End If*/
	) /*End Case*/
	] ;
  
If ( PatternCount ( interim ; "1" ) ; 1 ; 0 )
 )

Purpose: Determine whether Text contains any of the strings contained in SearchStrings ('or' logic) and that those strings are exact matches (useful when ESQL queries depend on the function). I found it useful, in concert with TableNames, LayoutNames, and conditional formatting, to determine whether entries for layout or table names were capitalized correctly.
Similar to Position function but will assess several SearchStrings, gives 0 or 1 (No or Yes) as Result
Restriction: the following !-containing strings may not be part of SearchStings: !c! !s! !p!
4/27/17 - A shortcoming of this occurred to me: this only checks the first incidence of the SearchStrings value in Text. Thus, ContainsExact ("Remark Mark"; "Mark") would evaluate as 0 because the first "mark" is not an exact match for the SearchString. I haven't yet come up with a fix for this. 4/27/17 - Solved it! Combo of Substitute & PatternCount

Comments

		Dick Impens, MaltaCode Dec 28, 2021
There is a slight problem when searching for numbers; 2nd floor flat 2 will return a 1 when using the function; 3rd floor flat 3 will return a 0 when using the function; 2nd floor flat 3 will also return a 1 (as the first number is a 2) but this is a word ("2nd", no spaces) so it should not be a exact match. Is this something that can be resolved in the function?

		Dick Impens, MaltaCode Dec 28, 2021
Even FM will see it as 1 word; LeftWords ( "2nd floor flat 3" ; 1 ) will return "2nd"

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

ContainsExact ( Text ; SearchStrings )

Comments

Support this website.

Support this website.