Thomas’ Developer Blog

March 21, 2008

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.

Advertisements

3 Comments »

  1. Appreciating the hard work you put into your site and in depth
    information you provide. It’s nice to come across a blog every
    once in a while that isn’t the same outdated rehashed information. Excellent read!
    I’ve saved your site and I’m adding your RSS feeds
    to my Google account.

    Comment by SEO aus Berlin — October 5, 2014 @ 9:50 pm

  2. What’s up to all, how is everything, I think every one
    is getting more from this website, and your views are good in favor of new people.

    Comment by rovine bobby valentino slow down (radio) lyrics — October 7, 2014 @ 9:42 pm

  3. I am really loving the theme/design of your blog. Do you
    ever run into any internet browser compatibility problems?
    A small number of my blog audience have complained about
    my site not operating correctly in Explorer but looks
    great in Chrome. Do you have any suggestions to help fix this
    issue?

    Comment by Der Scheidungsanwalt — October 8, 2014 @ 12:02 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: