Forum Discussion

JeffLove's avatar
JeffLove
Qrew Trainee
3 years ago

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...

Kind Regards,
Jeff Love

Formulas and functions
  • JeffLove's avatar
    JeffLove
    Qrew Trainee
    3 years ago
    Ooops on my formula
    var text A = SearchAndReplace([Live Field A],"\n"," ");
    var text B = SearchAndReplace($A,"<br>"," ");

    Trim(Upper($B))


    ------------------------------
    Thanks, Jeff
    ------------------------------
    • JeffLove's avatar
      JeffLove
      Qrew Trainee
      3 years ago

      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
      ------------------------------