Question: How can I remove “spaces” after a string that are caused by certain key combinations?
(1) I am ingesting a “Name” field from an outside data source.
(2) When you look at the “Name” data - it looks like (for example) "John “. I am looking at the Name data (a) from the raw data set (b) After ingesting it into Mitto and © Querying it from our data warehouse once ingested
(3) However, when I do a LENGTH function on it, it returns a longer length. For example, a LENGTH of John should result in 4, but I am getting a result of 6.
(4) Even if I apply a TRIM function to the name field (e.g. trim(name)) - I am STILL getting a returned LENGTH of 6. E.g. length(trim(name)) = 6
(5) When I send this field to an FTP site, the FTP site is ingesting this field as “JOHN A”. The A has an accent hat on it.
(6) FYI1 - According to Google, the A with an accent hat is called a circumflex accent – How to Insert Symbols Above Letters With the Keyboard | Small Business - Chron.com
(7) FYI2 - You can see an A with an accent hat (in Word for example) using a combination of keys - – specifically Pressing “Ctrl-Shift” and the caret (” ^ ") key and then the letter to insert a circumflex accent
(8) I don’t have the ability to change the actual raw data, so I need to CLEAN the data by removing what looks like a SPACE after the name, but when ingested on an FTP site is actually a circumflex accented A.