Difficulty with Less Than or Equal To sign 2

[mpdillon]

I am using VB.net to read an XML file in to SQL server. I am using VBs XML parser to read nodes, elements, etc. When an element's value contains a LE symbol and I insert the string in SQL, the resultant SQL record contains an Equal sign and not an LE.
I cannot examine the string for this character before inserting into SQL because I cannot find an ANSII equivalent. Normally I could find it with iPositiion = Instr("XML String", chr(??)). That does not work because there does not seem to be a number for the ?? that represents a LE. Someone suggested using 263 but that is a small o with a accent. iPosition = Instr("XML String", chr(263)) returns a -1, i.e. chr(263) was not found.
If I could find the string before it is inserted into SQL Server I would replace it with the HTML equivalent, ≤ .

Does anyone have any ideas how to do this?
Thanks,
pat

 

[tsuji]

What are LE, EQ and chr(263)? Do you have a reference webpage using that and showing that?

 

[mpdillon]

tsuji,
In HTML they are:

LE Less Than or Equal To ≤
GT Greater Than or Equal To >

chr is a Visaul basic function that returns the ASCII character of the number. The opposite function that gives the number of a character is Asc("="). That Asc function returns 61. The ASCII code for the Equal sign is 61.

I uploaded a small sample XML file to my web site. Search for 0.7 and you will see the Less than or equal to sign.

http://www.PatrickDillonPhotography.com/computer/small.xml

Thanks,
pat

 

[tsuji]

A quick check suggests you should be able to use instr() with ChrW(&H2264) to check for the existence of LE symbol. It does not mean that it solves all the possible problems because encoding can be very intriguing. Can you check it that alone is enough for your processing need?

 

[MakeItSo]

Is your SQLServer field properly encoded, i.e. as ntext or nchar?

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[mpdillon]

tsuji,
That was exactly what I needed. I didn't know about ChrW. I am on my way to work for another client now but will investigate more this evening. Where did you find the code &H2264 for the LE sign? Was it a web page? I ask because I would like to be able to look up other symbols. Does that type of code have a name like ASCII character set or HTML Characters, that I could google?

MakeItSo,
I am not sure. I am storing it in a varchar field. Is that appropriate? I will read this evening what the differences are. But frankly I do not know what difference would be important. Could you describe what I should be looking for in a field type?

Thank you both.
pat

 

[tsuji]

[0] If you have the xml in serialized persistent form, the matter is often easier to handle. You have always to have a hex-editor (plenty of free download) at hand. The sample you loaded shows clearly the xml is encoded in utf-16 little endian with bom. Use an hex-editor to load it, you can easily discover the utf-16 encoded character of "less-than or equal to" being 0x2264.

[1] Unicode characters are listed in more webpage than I can even browse at. This site has comprehensive data on this and other symbols.

http://www.fileformat.info/info/unicode/char/2264/index.htm

 

[MakeItSo]

I am not sure. I am storing it in a varchar field. Is that appropriate?

There you go.
char and varchar are for storing non-Unicode character data.
The Less-than-or-equal sign however is a Unicode character.
You should alter your field type to nchar or nvarchar.

Then also make sure to store that string as a Unciode string. I see you are using VB or VBA to store that string. You can store Unicode values by adding "N" directly before the single-quoted value:

strSQL = "INSERT INTO dbo.myTable (MyField) VALUES ([b]N[/b]'" & myString & "');"
Con.Execute strSQL

;-)

Cheers,
MakeItSo

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell