In an MS Access 2007 project report, I have the following (redacted) query:
SELECT SomeCol FROM SomeTable
The problem is, that SomeCol
apparently contains some invisible characters. For example, I see one result returned as 123456
but SELECT LEN(SomeCol)
returns 7
. When I copy the result to Notepad++, it shows as ?123456
.
The column is set to TEXT
. I have no control over this data type, so I can't change it.
How can I modify my SELECT
query to strip out anything non-numeric. I suspect RegEx is the way to go... alternatively, is there a CAST
or CONVERT
function?