VBSCRIPT REPLACE not removing spaces from Decrypted fields

Question

Got quite a head-scratcher....

I'm using the VBScript function REPLACE to replace spaces in a decrypted field from a MSSQL DB with "/".

But the REPLACE function isn't "seeing" the spaces.

For example, if I run any one of the following, where the decrypted value of the field "ITF_U_ClientName_Denc" is "Johnny Carson":

REPLACE(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc")," ","/")
REPLACE(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc"),"&nbsp;","/")
REPLACE(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc"),"Chr(160)","/")
REPLACE(CSTR(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc"))," ","/")
REPLACE(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc")," ","/",1,-1,1)
REPLACE(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc")," ","/",1,-1,0)

The returned value is "Johnny Carson" (space not replaced with /)

The issue seems to be exclusively with spaces, because when I run this: REPLACE(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc"),"a","/")

I get "Johnny C/rson".

Also, the issue seems to be exclusively with spaces in the decrypted value, because when I run this:

REPLACE("Johnny Carson"," ","/")

Of course, the returned value is "Johnny/Carson".

I have checked what is being written to the source of the page and it is simply "Johnny Carson" with no encoding or special characters.

I have also tried the SPLIT function to see if it would "see" the space, but it doesn't.

Finally, thanks to a helpful comment, I tried VBS REGEX searching for \s.

Set regExp = New RegExp
regExp.IgnoreCase = True
regExp.Global = True
regExp.Pattern = "\s" 'Add here every character you don't consider as special character
strProcessed = regExp.Replace(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc"), "?")

Unfortunately, strProcessed retruns "Johnny Carson" (ie. spaces not detected/removed).

If I replace regExp.Pattern = "a", strProcessed returns "Johnny C?rson".

Many thanks for your help!!

it might be due to encoding, did you try regular expressions? Example: https://stackoverflow.com/questions/28853070/replace-special-characters-in-vbscript `\s` is for whitespace, `\S` is for non-whitespace. — user2316116, Mar 31 '20 at 17:39
Not REGEX but I tried REPLACE with every variant of space listed in the solution here: https://stackoverflow.com/questions/8515365/are-there-other-whitespace-codes-like-nbsp-for-half-spaces-em-spaces-en-space. I'll try the REGEX approach to see it it "sees" the decrypted space. — nbardach, Mar 31 '20 at 17:52
Just tried with REGEX but it doesn't "see" the space either (see updates to question). — nbardach, Mar 31 '20 at 18:03
hm. so you're saying `asc(mid(thatstring,7,1))` returns 32 (which is for space)? — user2316116, Mar 31 '20 at 18:21
Response.Write(asc(mid("Johnny Carson",7,1))) returns 32 but Response.Write(asc(mid(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc"),7,1))) returns 15712 (𕜒 Invalid Character). Still, REPLACE(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc"),"𕜒","/",1,-1,1) returns "Johnny Carson" (ie. no 𕜒 found). — nbardach, Mar 31 '20 at 19:45
More info... this site (https://www.fontspace.com/unicode/analyzer#e=Sm9obm55IENhcnNvbg) parses the space in ITF_U_Ledger.Fields("ITF_U_ClientName_Denc") ("Johnny Carson") as U+0020 but when I try REPLACE(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc")," ","/"), I still get "Johnny Carson" (ie. it doesn't see the ). — nbardach, Mar 31 '20 at 20:04

user2316116 · Accepted Answer · 2020-03-31T22:24:29.823

1

As we found, the right character code is 160, and that did the trick:

replace(..., ChrW(160), "...")

This seems to be data specific and, additionally, as an alternative you can try to get same encoding of the source script (i.e. save with Save As with Encoding), or convert received database value into a different target encoding.

edited Mar 31 '20 at 22:24

answered Mar 31 '20 at 20:43

user2316116

6,726
1
21
35

Actually the character shown by ASC() is -15712 but REPLACE(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc"),ChrW(-15712),"/"), REPLACE(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc"),ChrW(15712),"/") and REPLACE(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc")," ","/") all display "Johnny Carson" (ie. no space found) :( – nbardach Mar 31 '20 at 21:15
1

if trying AscW which is for double-byte character set i.e. `Response.Write(AscW(mid(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc"),7,1)))` do you see same -15712? Also did you try to assign db value to a local string variable, i.e. `s = ITF_U_Ledger.Fields("ITF_U_ClientName_Denc")` and then replace? – user2316116 Mar 31 '20 at 21:26
Y, I assigned the field to a variable before trying to replay and also tried strFullName = CSTR(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc"). When I run the AscW version, I get 160. Is that a clue? – nbardach Mar 31 '20 at 21:38
1

160 stands for / non-breaking space https://en.wikipedia.org/wiki/Non-breaking_space i.e. it looks like this is not UTF or ASCII but ANSI. Please try `replace("...", ChrW(160), "...")` – user2316116 Mar 31 '20 at 21:55
1

That was it! strFullName = CSTR(ITF_U_Ledger.Fields("ITF_U_ClientName_Denc")) strFullName = REPLACE(strFullName,ChrW(160),"/") produces "Johnny/Carson". Want to rework this as the answer and I'll give you credit for the solution? – nbardach Mar 31 '20 at 22:01
Many thanks for your help! Marked your answer correct,. Be well and stay healthy! – nbardach Mar 31 '20 at 22:31

VBSCRIPT REPLACE not removing spaces from Decrypted fields

1 Answers1