Another self note…
My coworker was cleaning up data in database as many data were entered with white space in it.
He did a quick
ltrim(rtrim(column)) to quickly get rid of spaces but didn’t work.
As he had requested for help, I asked him to open Notepad++ and show all symbols.
But it wasn’t showing the white text at the end of the text.
So I asked him to him to copy the white space then fire up PowerShell as I needed to see if the white space is indeed
32 in ASCII value.
|PS C:\misc\sources> [int][char]' '|
It turns out it was 160 meaning not a regular space thus can’t be trimmed using
rtrim, which only trims white space of value 32.
So the fix was to
replace(column, char(160), ''), which took care of the issue.