Discussions

 View Only
  • 1.  stripping hidden characters from text strings to enable field equality

    Posted 08-10-2022 19:07
    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



  • 2.  RE: stripping hidden characters from text strings to enable field equality

    Posted 08-10-2022 19:11
    Ooops on my formula
    var text A = SearchAndReplace([Live Field A],"\n"," ");
    var text B = SearchAndReplace($A,"<br>"," ");

    Trim(Upper($B))


    ------------------------------
    Thanks, Jeff
    ------------------------------



  • 3.  RE: stripping hidden characters from text strings to enable field equality

    Posted 10 days 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
    ------------------------------