Brian Dunning's FileMaker Custom Functions

SubstituteCaseInsensitive ( text ; searchString ; replaceString )

Works just like FileMaker's Substitute( ) function, but case is ignored.

  Average rating: 4.5 (28 votes) Log in to vote

Jeremiah Hammond   Jeremiah Hammond
DB Services
http://www.dbservices.com

Share on Facebook Share on Twitter

  Sample input:
SubstituteCaseInsensitive ( "abAbaBAB123a4b7aB" ; "aB" ; "zz" )
  Sample output:
zzzzzzzz123a4b7zz

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

Returns a text string with every occurrence of searchString in text replaced by replaceString in text. Works just like FileMaker's Substitute( ) function, but case is ignored.

Good for standardizing the case of a string across multiple records, where the case wasn't previously controlled, such as migrating hand-typed status or category fields from one system to another. Also useful for removing all instances of a string across multiple records, where that string could have variable case.

Example:

"I Do noT liKe them in a box.
I Do Not Like them with a fox
i do nOt likE them in a house
i do not like them with a mouse"

will be changed to:

"I do not like them in a box.
I do not like them with a fox
I do not like them in a house
I do not like them with a mouse"

with one call of the SubstituteCaseInsensitive( ) function, where the call is SubstituteCaseInsensitive( theFirstExampleParagraphAbove, "I do not like" ; "I do not like" )

Will replace up to the 1st 10,000 instances of the searchString (based on FileMaker's recursive function call stack limit).

It goes without saying that this function doesn't support the multiple-search-and-replace bracket notation that the native Substitute( ) function supports, as FileMaker doesn't provide the ability to define optional parameters in a custom function.

 

Comments

Stannis Baratheon   Stannis Baratheon, Dragonstone
May 16, 2014
It'd be great if this followed FileMakers native syntax allowing for multiple pairs of substitute.

SubstituteCaseInsensitive(text;[“a”;“A”];[“b”;“B”])
 
Doug Staubach   Doug Staubach, Denver, Colorado, USA
Mar 22, 2015
Nicely done - thank you!
 

Log in to post comments.

 

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

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: