I did find a solution to this eventually, in case others have a similar problem.
It turns out using the urlencode() function at the start converts all the strange characters, and there are a bunch of them into things like %0D%0A %0A %3A
So you can basically trim out the garbage by SearchandReplacing all the different weird characters and replacing them with a space "%20"
Upper(Trim(SearchAndReplace(SearchAndReplace(SearchAndReplace(SearchAndReplace(URLEncode([My TEXT Field]), "%0D%0A", "%20"), "%0A", "%20"), "%20", " "), "%3A", ":")))
Then the text comparison is equivalent.
------------------------------
Thanks, Jeff
------------------------------
Original Message:
Sent: 08-10-2022 19:11
From: Jeff Love
Subject: stripping hidden characters from text strings to enable field equality
Ooops on my formula
var text A = SearchAndReplace([Live Field A],"\n"," ");
var text B = SearchAndReplace($A,"<br>"," ");
Trim(Upper($B))
------------------------------
Thanks, Jeff
Original Message:
Sent: 08-10-2022 19:06
From: Jeff Love
Subject: stripping hidden characters from text strings to enable field equality
I am using a staging table to compare a .CSV import to a bunch of live data.
I have maybe 50 fields to compare and I am trying to check a few thousand records regularly and just flag the fields that have changed.
This is typically pretty straightforward as they are text fields and using a List function and a bunch of if statements, I can easily display the field names when Live Field A <> Import Field A.
My issue is lots of text fields are appearing as not equal, though they look the same when displaying them. I have used the tricks I know to tidy up hidden characters...
var text A = SearchAndReplace([Live Field A],"\n"," ");
var text C = SearchAndReplace($B,"<br>"," ");
Trim(Upper($B))
This removes next line and carriage returns and makes all uppercase and trims extra spaces. I apply this to both fields, they have the same length of characters, look identical, even copy/pasting them to Notepad++ and turning on hidden characters they look the same but according to QB they are not equal.
I have one more question path of enquiry... I have tried swapping between Text and Rich Text. The <br> I think works in Rich Text but \r in text gets converted when I save to something else, perhaps that is the issue?
Any text gurus out there?
Ideas or assistance would be appreciated...