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