Jump to content

Case INsensitive substitute possible?


Recommended Posts

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.

Link to post
Share on other sites
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 by comment
  • Like 1
Link to post
Share on other sites

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

Link to post
Share on other sites
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. 

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.