Case INsensitive substitute possible?

[Hijack]

Anyone have a suggestion on how to compensate for the substitute function being case sensitive?

I'm trying to create a simple text highlighting feature in a database.  I don't want to alter the original source text field so I'm creating a calculated field that references the original field with a simple substitute function to highlight text:

Substitute ( Data_Parts::Content ; Data_Parts::Part_Filter_Value ; TextColor ( TextStyleAdd ( Data_Parts::Part_Filter_Value ; HighlightYellow ) ; RGB ( 0 ; 0 ; 0 ) ) )

A user enters search text in the Parts_Filter_Value field and that's passed through the substitute function to the calculated field to highlight the entered text.  However, since substitute is case sensitive, if a user enters (for example) a name in lower case like "david" but the text in the source field has David in as proper case, the substitute fails and the target word/string isn't highlighted.  I could compensate for base initial cap mismatch but there are other situations with strings where the case may be mixed (ex: a last name like McKinney or some such situation).

Anyone done anything similar successfully or know of a custom function that will do a case insensitive substitute?

Thanks all.

[comment]

21 minutes ago, Hijack said:

substitute is case sensitive

True, but PatternCount() and Position() are not, so you could use that instead. Are you really using version 17? This requires recursion, so if you cannot use the While() function, you will need the Advanced version in order to install a custom function.

If you can use a custom function, have a look at:
https://www.briandunning.com/cf/578

 

Edited January 7, 2021 by comment

[Hijack]

Thanks comment.  You're the most prolific FM guru on the planet I think.

I've updated my FM version reference in my profile to 18.  Thanks for that catch.

I just installed the custom function you mentioned and adjusted my calculation and it works perfectly.  I had seen other posts on that specific function but they seemed to indicate it just highlighted the first occurrence.  I guess that was either wrong or the function has been updated since the posts I came across.

Anyway, thanks for the assist.  Things are working now the way I need them to.

-David

[comment]

28 minutes ago, Hijack said:

I had seen other posts on that specific function but they seemed to indicate it just highlighted the first occurrence.  I guess that was either wrong or the function has been updated since the posts I came across.

I haven't seen such posts. And I don't recall ever updating the function. 

 

[Hijack]

There's also the distinct possibility my wife slugged me in the head in the middle of the night which has altered my take on reality.  Didn't realize you were the author of the function.  Fantastic contribution!  Thanks again.

[comment]

1 hour ago, Hijack said:

my wife slugged me in the head

I am under the impression that these days this is no longer a prerequisite...