February 6, 2009 at 2:32 am
Hello,
we had a HP3000 server with a (I think) hierarchical database migrated to a SQL Server 2000 database. The data was migrated with the "old" charset (HP8). To retrieve data with correct special characters you have to use a conversion function (on a field basis). The function runs through the characters, converts them to ASCII, replaces the ASCII values (CASE WHEN), converts from ASCII back to character, merges the characters back to a string and returns the value.
That works perfecly but the performance is really poor. Does someone have an idea for a better algorithm or a SQL Server 2000 built-in conversion mechanism from HP8 to Latin charset?
Peter
PS: Please correct me if I am wrong. I never heard of HP8 before (the conversion function indicated that naming) but I guess it is a charset called HP8 or HP Roman8.
February 6, 2009 at 2:53 am
Hi Peter,
This is a good issue and I have no immediate answers to improve on your current strategy, but I will continue to research.
In the mean-time, here is a good link for HP8 character set for review.
http://www.minisoft.com/pages/general/manuals/pdf/Roman8_CSC.pdf
Thanks,
Phillip Cox
SQL Server Consultant
MCITP - DBAdmin - SQL Server 2005
MCTS - SQL Server 2005
MCP - SQL Server 2000
February 6, 2009 at 3:26 am
Hi Phillip,
thank you for your help! I really appreciate any further information and ideas to resolve my problem. Until then, here is the source code of the replace part of the function:
SET @asc = ASCII(SUBSTRING(@str, @pos, 1))
IF @asc > 127
SET @asc =
CASE @asc
WHEN 204 THEN 228 -- ä
WHEN 206 THEN 246 -- ö
WHEN 207 THEN 252 -- ü
WHEN 216 THEN 196 -- Ä
WHEN 218 THEN 214 -- Ö
WHEN 219 THEN 220 -- Ü
WHEN 222 THEN 223 -- ß
WHEN 243 THEN 181 -- µ
WHEN 197 THEN 233 -- é
WHEN 196 THEN 225 -- á
WHEN 210 THEN 216 -- Ø
WHEN 179 THEN 176 -- °
WHEN 209 THEN 238 -- î
WHEN 192 THEN 226 -- â
WHEN 195 THEN 251 -- û
WHEN 213 THEN 237 -- í
WHEN 198 THEN 243 -- ó
WHEN 200 THEN 224 -- à
WHEN 201 THEN 232 -- è
WHEN 220 THEN 201 -- É
WHEN 224 THEN 193 -- Á
ELSE @asc
Regards,
Peter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply