I have users that compose text in Word and copy/paste into a Notes Field in one of my databases. The problem is when the use double quotes in word, Word uses unicode values of 8220 and 8221 to represent an opening double quote and closing double quote.Notes handles this fine, however, we send this data to SQL Server and a .Net application that does not! I need to strip these out prior to send the data on it’s way.
I have tried everything I can think of including @ReplaceSubString and @Ascii but I can’t seem to get rid of them.
Subject: How can I remove special characters from a text field
I use the following function to strip control characters (tab, line feed, etc.) from strings. If you know the ASCII code of the characters causing your problems, you should be able to modify the code for your needs.
–Ron
Function StripControlChars(sString As String) As String
'This routine strips all of the control characters from a string. Control characters
'are defined as those whose ASCII value is less than 32 or greater than 127. The
'control characters will be replaced with a space if the character immediately preceding
'the control character is not a space too.
'
'Parameters:
' sString = String to be checked for control characters.
'
'A sample call would be:
' Doc.Comments = StripControlChars(Doc.Comments(0))
Dim iCntr As Integer
Dim iLen As Integer
Dim sChr As String
Dim sLast As String
Dim sNew As String
iLen = Len(sString)
sLast = "~"
sNew = ""
For iCntr = 1 To iLen
'Check each character. If it is a control character and the last
'one was not a space, change the control character to a space.
sChr = Mid$(sString, iCntr, 1)
If (Asc(sChr) < 32 Or Asc(sChr) > 127) And sLast <> " " Then sChr = " "
If Asc(sChr) >= 32 And Asc(sChr) <= 127 Then sNew = sNew & sChr
sLast = sChr
Next
StripControlChars = sNew 'Return the new string.