VBSCRIPT REPLACE not removing spaces from Decrypted fields

user_1569876 0
Viewed 167

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")," ","/")
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!!

spaces vbscript encryption sql-server replace
1 Answers

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.

user_23161160
Related