Spatiality (a short interlude with a hint of bitterness)

Importing data to SQL Server needs its own entry in this blog. Especially when it comes to international characters. You know: 'Ö', 'Ü', 'Ñ', 语 and the likes. I am talking about UTF-8, UTF-16 and other ways to map a character to bits and bytes.

Character encoding.

The problems started in 1963 with ASCII (and probably earlier in the analog world with Morse codes, viking runes and smoke signals). ASCII and IBM's ECBDIC managed to fit a character into seven and eight bits respectively. Memory space was a very limited resource those days so all was good until someone realized that there actually exists other languages than English and they tried to hack it into ASCII using ISO 8859.

This time they forgot small regions such as the Arabic world, Japan, India and China...

1988 they finally started working on UNICODE and nowadays we only have one way to save international characters: UTF-8, UTF-16 and UTF-32...

Most stuff you find on Internet is UTF-8, SQL Server handles UTF-16 and when you import stuff into the database without spending an hour of googling ahead of the task everything ends up in the non UTF fields called varchar...

Some people probably love code pages and character encodings. I don't. For me it is one of those totally uninteresting side issues that distracts my creativity from really achieving something. It is fully in the line with issues such as coordinate transformations, spending time fixing web stuff to make it readable in any browser and trying to access web services not made for Microsoftians as myself.

I hate it.

Give me one type of coordinate, browser, service and one type of string!

I want to use my brain for content and functionality, not details.

So... this long rambling obviously cooks down to the fact that I goofed up and managed to import all that data in spatiality 3 to a varchar field instead of an nvarchar field. And as I have a blog I have the opportunity to channel my bitterness...

Getting the stuff all the way to Azure made it even less interesting to go through the whole process again and try to import the data 'the right way'.

So I googled.

Ye who google shall find.


I found this. A function that fixed my mistakes and saved a good day of work for me. I just added an nvarchar to the table, updated it with the function and dropped the old one. Worked like a charm.

I love google and I love people like Jason that cleans up after my mistakes. Thank you!