Menu
My client has an old MS SQL 2000 database that uses varchar(50) fields to store names. He tried to use this database to capture some data (via a web form). Some of the form-fillers are from other countries, and the varchar fields went nutty when some of these folks entered their names. Is it possible to recover the data somehow? Maybe by guessing what the character should be based on what it resolved to in ASCII/varchar and the country the person is from? Some of the data:
Name / Country / First or Last Name?
Jiřà / CZE / F
Torbjörn / FIN / F
Huszár / HUN / L
Jürgen / DEU / F
Müller / CHE / L
Bumbálková / CZE / L
Doležal / CZE / L
Loïc / DEU / L
By the way, the web form specified this content-type:
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
574
VARCHAR columns, as the name implies, store variable-length data. They can store characters, numbers, and special characters just like a CHAR column and can support strings up to 8000 bytes in size.
Unicode Data Types. Data types nchar, nvarchar, and long nvarchar are used to store Unicode data. They behave similarly to char, varchar, and long varchar character types respectively, except that each character in a Unicode type typically uses 16 bits.
The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.
ASCII character set is captured using regex [A-Za-z0-9]. You can use this regex in your query as shown below, to find non-ASCII characters. mysql> SELECT * FROM data WHERE full_name NOT REGEXP '[A-Za-z0-9]'; You can also customize regex to include certain characters.
Working from the 5th example.
à is ascii #195 (C3). ¼ is ascii #188 (BC).
I'd guess that Müller is meant to be Müller.
If this is UTF-8, based upon http://en.wikipedia.org/wiki/UTF-8#Description
We've got C3 BC = 1100 0011 1011 1100
Applying the UTF-8 mapping:
(110) 00011 (10) 11 1100
0000 0000 1111 1100
00FC which is Unicode ü
U+00FC (see http://en.wikipedia.org/wiki/Latin_characters_in_Unicode)
Seems to me that you could work through this programmatically.
Now solving the first example:
Jiå™ã was actually Jiřà (The final character not shown).
Ignoring the Ji, which is correct,
C5 99 c3 AD
(110)0 0101 (10)01 1001 (110)0 0011 (10)10 1101
0159 00ED
ří
So the name is: Jiří. Wikipedia says that special r is Czech and so is the i. Furthermore if I google Jiří (http://www.google.com/search?q=Ji%C5%99%C3%AD&ie=utf-8&oe=utf-8) I get plenty of hits. We're on a winner here.
The second example, Torbjörn, maps nicely to Torbjörn which sounds convincing.
IMHO there's no great need for human checking of these, they seem to just work.
193
answered Sep 21 '22 14:09
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
