Forum Discussion

JeffLove's avatar
JeffLove
Qrew Trainee
2 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
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
2 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
------------------------------