Unicode in databases (ANSI to Unicode)

Alright this is one of those stupid things that seems easy, but took me forever to figure out… oi.

Here’s the deal.  We have a database and we want to go ahead and store some Unicode characters.  Those being japanese, chinese, greek, or whatever language character you want to insert.

The problem is some databases do not allow such characters to exist.  So to place those into a database we need to store their HTML entity counterpart.

 To do this we would normally use CHR() and ASC() in ASP, but the problem is that these functions only support latin characters, your classic a-Z or 0-9 etc. characters. 

To solve this we simply add a W… which is what took me forever to figure out.

Most people know about chrW() but ascW() is not half as well known, which made it difficult for me since no one knows of its existance for the most part.

Anyway what do these functions do?  Well instead of your classic ANSI/Ascii characters these support UTF-8 characters to.  So you can simply insert your character you need a code for such as: ascW(“正”) which will return the unicode for the kanji correct.  (if you see a block it’s because your computer doesn’t support unicode language characters) This will return 27491.  Now that we got that we simply use 正 to insert into the database to return the correct character!  Simple, ne?

So now we need to convert a string into something that can be stored and read in a database, your answer would be:

  Dim i As Integer
  Dim s As String = “The kanji for complete is: 正.”

  Dim mystr As String

  for i = 1 to len(s)
   If ascW(mid(s,i,1)) > 126 then
    mystr += “&#” & ascW(mid(s,i,1)) & “;”
   Else
    mystr += mid(s,i,1)
   End if
  next

this code will cycle through the string and replace any character with a unicode over 126 (which are the ones asc doesn’t support typically)  once you get the new string you can store mystr into your database and you’re good to go!

Hope this helps everyone with some basic HTML entity/database support!

 PS: you should likely have some soft of notice to users who see square boxes just in case they get confused.