Remove trailing invisible character(s) from text via VBScript
ms-access, utf-8, vbscript
Solution
`Trim()` will remove trailing spaces. So, if your string values end with something that looks like a space, but is not removed by `Trim()`, it may not actually be a space character. Another possibility would be a space followed by an invisible character.
Either way, it would be useful to know what the last character actually is.
SELECT Asc(Right(your_string, 1)) AS ascii_value
FROM YourTable;
Once you know what that character is, you can decide how best to get rid of it.
I don't know what the best way is for you. From the details which have emerged in the comments, it appears the troublesome character is a non-breaking space, ASCII 160. You want to discard that character when it occurs at the end of a string. Perhaps you also want to discard any regular space characters which precede the non-breaking space in that situation. But if non-breaking spaces are present elsewhere in the string, you want to keep those.
Additionally it seems you want to do this for existing data stored in the Access table and for new incoming data collected with your VBScript scraping procedure. From VBScript, a query can't use custom VBA functions or the `Replace()` function. So I don't know what to say about the new data without seeing your scraping code. For existing data, you could use an `UPDATE` statement, similar to the one you suggested in your question, which should work from either VBScript or from within an Access session.
UPDATE YourTable
SET your_string = Left(your_string, Len(your_string) -1)
WHERE Asc(Right(your_string, 1)) = 160;
If you also want to discard any spaces which are present ahead of the trailing non-breaking space, incorporate `Trim()`.
UPDATE YourTable
SET your_string = Trim(Left(your_string, Len(your_string) -1))
WHERE Asc(Right(your_string, 1)) = 160;
Problem
I am scraping some web data and inserting into Access 2007. Using a .vbs script to fetch and insert data. Things were fine, until a recent upgrade of the scrapping program. There is an extra space in each text record towards the end. Seems related to the encoding. Trim - function does not work on this. Tried Replace A (Circumflex) , and xA0 , no result. Doing - Left(mystring,len(mystring)-1) right now. Since I would be scraping millions of records, what would be the simplest and correct way to solve this? Also, how to rectify this as the existing data in Access will now contain such 'contaminated' data? Use an UPDATE statement? How to check for last character as space?