Newest functions Upload a function Leave a tip My account

There are 2,521 functions in this library.

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 DB Services http://www.dbservices.com

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

/* Purpose: 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. Returns: Text Parameters: (text) text = The text where you want to substitute the search string with the replace string. (text) searchString = The string to be replaced in the piece of text. (text) replaceString = The string replacing the search string. Dependencies: None 2014-05-16, JH, Created function */ Let( [ // ******GRAB PARAMETERS****** vText = text ; vSearchString = searchString ; vReplaceString = replaceString ; // ******DETERMINE COMPONENTS FOR SUBSTITUTION****** vSearchStringLength = Length( vSearchString ) ; vSearchStringStartPos = Position( vText ; vSearchString ; 1 ; 1 ) ; vSearchStringEndPos = If( vSearchStringStartPos > 0 ; vSearchStringStartPos + vSearchStringLength - 1 ; 0 ) ; vTextLeftOfSearchString = If( vSearchStringStartPos > 0 ; Left( vText ; vSearchStringStartPos - 1 ) ; vText ) ; vTextRightOfSearchString = If( vSearchStringStartPos > 0 ; Right( vText ; Length( vText ) - vSearchStringEndPos ) ; "" ) ; // ******SUBSTITUTE****** vSubstitutedText = vTextLeftOfSearchString & If( vSearchStringStartPos > 0 ; vReplaceString ; "" ) & If( not IsEmpty( vTextRightOfSearchString ) ; substituteCaseInsensitive( vTextRightOfSearchString ; vSearchString ; vReplaceString ) ; "" ) ] ; vSubstitutedText ) Click to copy

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, 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 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 20 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: