Replace special characters with escape sequences (1 Viewer)

T

Tea

Member
Local time
Today, 10:01
Joined
Oct 30, 2020
Messages
51
I'm trying to find a way to replace special characters (<, >, &, ', ") in my description field with the escape sequences, so I can use the newString in a string, that is later on use to generate a xml file.

Code: 
Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
Next

The problem is when I try to put the line with ampersand into the code, it gives a weird string like this:
Code: 
<ItemDesc>Test &#38;#38;#38;lt; &#38;#38;#38;gt; &#38;#38;#38;#39; &#38;#38;#38;</ItemDesc>

when it should look like this.
Code: 
<ItemDesc>Test < > & </ItemDesc>

With the character <, > and ' it works prefectly fine.

Is there a way to resolve this matter with a different approach?
 
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:01
Joined
May 7, 2009
Messages
19,247
why do you need to Replace those characters when creating an XML?
have you tried without converting them?
 
T

Tea

Member
Local time
Today, 10:01
Joined
Oct 30, 2020
Messages
51
Yes I did. The xml parser throws an exception and the xml file is created without the CDATA tags. So it's just a one line string without tags
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 09:01
Joined
Sep 21, 2011
Messages
14,336
Sub the & first?, before you put any of the replacement characters with & there?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:01
Joined
May 7, 2009
Messages
19,247
what i can see is in your code is that you are Replacing it with "&...(etc).
but later on those "&..(etc) (replacement) will be replaced Again, sample:

< will be replaced with "&alt;"
..
..
on the later part of the code this will again replaced:

"&alt;" ==> (replace the & with "&#38;") ==> &#38;alt;

*** therefore put the last part of your code to the first.

this:
Code: 
Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
Next

will become:
Code: 
Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
Next
 
T

Tea

Member
Local time
Today, 10:01
Joined
Oct 30, 2020
Messages
51
arnelgp said:
what i can see is in your code is that you are Replacing it with "&...(etc).
but later on those "&..(etc) (replacement) will be replaced Again, sample:

< will be replaced with "&alt;"
..
..
on the later part of the code this will again replaced:

"&alt;" ==> (replace the & with "&#38;") ==> &#38;alt;

*** therefore put the last part of your code to the first.

this:
Code: 
Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
Next

will become:
Code: 
Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
Next
Click to expand...
I have tried that, but it's still not working. Has the same result.
Gasman said:
Sub the & first?, before you put any of the replacement characters with & there?
Click to expand...
What do you mean?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:01
Joined
May 7, 2009
Messages
19,247
do you have your Textfield formatted as Richtext?
on second thought, i don't know what we are doing here.
XML is different from HTM/HTML.
 
T

Tea

Member
Local time
Today, 10:01
Joined
Oct 30, 2020
Messages
51
arnelgp said:
do you have your Textfield formatted as Richtext?
on second thought, i don't know what we are doing here.
XML is different from HTM/HTML.
Click to expand...
I don't thnk it's necessary to have Rich text on this field as it's not for HTML purposes. I'm generating an XML through a form. But if a field contains those special characters it does not create the xml tags becuase XML can'T read these symbols without escaping them.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:01
Joined
May 7, 2009
Messages
19,247
remove the For...Next Loop:

myString = CleanString(rs![desc])


in separate Module:

Code: 
Public Function CleanString(ByVal var As Variant)
'Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim newString As String, char As Variant
newString = var & ""
If Len(newString) = 0 Then Exit Function
'For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
'Next
CleanString = newString
End Function

OR on your Original Code:

Code: 
'Remove this, no need
'Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
'Remove For Each Loop...
'For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
'Next
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 09:01
Joined
Sep 21, 2011
Messages
14,336
Yes, but you are looping for each character, so after the first loop, you have & and we can go on an on. :(
You need to just replace them once.
I'd probably have an array of what to look for and what to replace, else use a case statement inside the loop.
 
  • Like
Reactions: Tea
T

Tea

Member
Local time
Today, 10:01
Joined
Oct 30, 2020
Messages
51
arnelgp said:
remove the For...Next Loop:

myString = CleanString(rs![desc])


in separate Module:

Code: 
Public Function CleanString(ByVal var As Variant)
'Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim newString As String, char As Variant
newString = var & ""
If Len(newString) = 0 Then Exit Function
'For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
'Next
CleanString = newString
End Function

OR on your Original Code:

Code: 
'Remove this, no need
'Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
'Remove For Each Loop...
'For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
'Next
Click to expand...
I have remove the for each altogether, it works now.
 
T

Tea

Member
Local time
Today, 10:01
Joined
Oct 30, 2020
Messages
51
Gasman said:
Yes, but you are looping for each character, so after the first loop, you have & and we can go on an on. :(
You need to just replace them once.
I'd probably have an array of what to look for and what to replace, else use a case statement inside the loop.
Click to expand...
I have removed the for each loop, now it works fine. Made kinda sense, when the escape sequence has it's own &.
 
T

Tea

Member
Local time
Today, 10:01
Joined
Oct 30, 2020
Messages
51
Gasman said:
But do you understand why it was not working?
Click to expand...
Is there a different approach where the code would look cleaner? Is there a function that replaces especially these symbols? I would like to apply this to another field also, but it would mean I would have to repeat the same problem. If there was another & in the second string for the field [notes].

Code: 
Dim myString As String
Dim newString As String
Dim myString2 As String
Dim newString2 As String
myString = rs![desc]
newString = myString
    newString = Replace(newString, "&", "&amp;")
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
    newString = Replace(newString, Chr(34), "&#34;")
myString2 = rs![notes]
newString2 = myString2
    newString2 = Replace(newString2, "&", "&amp;")
    newString2 = Replace(newString2, "<", "&lt;")
    newString2 = Replace(newString2, ">", "&gt;")
    newString2 = Replace(newString2, "'", "&#39;")
    newString2 = Replace(newString2, Chr(34), "&#34;")
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 09:01
Joined
Sep 21, 2011
Messages
14,336
Tea said:
I have removed the for each loop, now it works fine. Made kinda sense, when the escape sequence has it's own &.
Click to expand...
Only partially, as you were looping for each character.
If you kept the order the same then you could use the char in the loop, but then you need to know what to replace it with, hence my choice of arrays.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 09:01
Joined
Sep 21, 2011
Messages
14,336
Tea said:
Is there a different approach where the code would look cleaner? Is there a function that replaces especially these symbols? I would like to apply this to another field also, but it would mean I would have to repeat the same problem. If there was another & in the second string for the field [notes].

Code: 
Dim myString As String
Dim newString As String
Dim myString2 As String
Dim newString2 As String
myString = rs![desc]
newString = myString
    newString = Replace(newString, "&", "&amp;")
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
    newString = Replace(newString, Chr(34), "&#34;")
myString2 = rs![notes]
newString2 = myString2
    newString2 = Replace(newString2, "&", "&amp;")
    newString2 = Replace(newString2, "<", "&lt;")
    newString2 = Replace(newString2, ">", "&gt;")
    newString2 = Replace(newString2, "'", "&#39;")
    newString2 = Replace(newString2, Chr(34), "&#34;")
Click to expand...
Use arnel's CleanString function.
 
You must log in or register to reply here.

Similar threads

T
Hyperlinks
Replies
9
Views
380
isladogs
isladogs
N
Code does not support certain characters such as full-width symbols or Chinese
Replies
1
Views
350
ebs17
E
ListO
Action Query Export transcodes characters to HTML escape sequences
Replies
6
Views
491
isladogs
isladogs
S
Refine a working regex exclude periods or dots except if a decimal
Replies
10
Views
573
DickyP
DickyP
R
Updating Excel spreadsheet from within the Access
Replies
8
Views
365
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom