April 26, 2012 at 11:18 am
opc.three (4/26/2012)
MisLead (4/26/2012)
opc.three (4/26/2012)
I think MisLead (ironic name given the conversation, no? :-D) wants to store the data as NVARCHAR, but wants to provide an interface for this legacy app to get that NVARCHAR data out as a VARCHAR where for each 2-byte NVARCHAR character 2 1-byte VARCHAR characters are returned. Similarly, when the legacy app wants to store to the database it would send a VARCHAR string containing pairs of 1-byte characters and the database code should then silently reverse the original conversion into NVARCHAR strings containing a string of 2-byte characters, and that is what would be stored to the table. Did I summarize that accurately MisLead?Ironic? I guess but i have not been lead so far-so not so ironic so far.
NO. I want to store it as VARCHAR(EXTENDED ASCII). I think I made that clear.
SELECT CAST(N'???' AS VARBINARY(MAX))-- CONVERTING UNICODE TO BINARYSELECT CAST(0x4772EE4E0D54 AS NVARCHAR(MAX))--CONVERTING BINARY BACK TO UNICODE
See how the conversion above is done withtout dataloss. Now my requirement is instead of binary/binary, it needs to varchar (extended ascii)
Not much has been made clear in your writings...except that you are not sure about what it is you're trying to do and are not willing to explain how this will be used....which is why your train of thought has been so hard to follow. It all goes back to volunteers trying to help you with your work, and not even receiving the common courtesy of answers to direct questions...I think I might be done here...
From your last response to me:
MisLead (4/25/2012)
You keep asking for answers from us, yet you won't answer the questions we ask?
Again, what is the business case for doing this? What other alternatives have been considered that could be better than the solution you are single-mindedly trying to pursue?
There are no alternatives atleast to the extent of my exploration. What can be done is convert those unicode characters to binary/varbinary and convert it back to unicode by the calling application if required but then thats not exactly the expected result. The requirement is somehow manipulate/tweak..??? sql code so that we can store these data in extended ascii format.
The bottom line requirement is to be able to contain unicode (non english) in non unicode characters without the loss of data. ie. store in varchar/char column.
If you note, you still haven't answered the question regarding the business case. What is it you are trying to accomplish AND why.
You say there are no alternatives, explain. Storing unicode data in unicode datatypes is the proper way to store the data.
April 26, 2012 at 11:35 am
Lynn,
Business Case is Simple. We have data flowing from a system that supports Unicode to the system that does not support Unicode and only supports ANSI standard characters. what would you suggest?
April 26, 2012 at 11:41 am
Still no answers on desired Collation of returned VARCHAR types...no answer to answer potential substitution issues if no E/ASCII code point is found...no answer on how exactly to strip apart binary to make VARCHAR from NVARCHAR since mapping examples are weak and prone to error posting characters in a forum....no confirmation on second attempt at a mapping example from a single Asian character to two E/ASCII characters...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2012 at 11:44 am
MisLead (4/26/2012)
Lynn,Business Case is Simple. We have data flowing from a system that supports Unicode to the system that does not support Unicode and only supports ANSI standard characters. what would you suggest?
Modify the system to hold unicode data. Attempts to convert unicode to non-unicode data and back can result in data loss.
April 26, 2012 at 12:13 pm
Lynn Pettis (4/26/2012)
MisLead (4/26/2012)
Lynn,Business Case is Simple. We have data flowing from a system that supports Unicode to the system that does not support Unicode and only supports ANSI standard characters. what would you suggest?
Modify the system to hold unicode data. Attempts to convert unicode to non-unicode data and back can result in data loss.
ya if only they would that- I didn't have to go through all this-but hats not happening, hence the quest for help.
April 26, 2012 at 12:16 pm
MisLead (4/26/2012)
Lynn Pettis (4/26/2012)
MisLead (4/26/2012)
Lynn,Business Case is Simple. We have data flowing from a system that supports Unicode to the system that does not support Unicode and only supports ANSI standard characters. what would you suggest?
Modify the system to hold unicode data. Attempts to convert unicode to non-unicode data and back can result in data loss.
ya if only they would that- I didn't have to go through all this-but hats not happening, hence the quest for help.
Like I said, good luck. I don't think you will be able to write lossless unicode to extended ASCII to unicode translation process. The best you may be able to do is convert the unicode data to varbinary and store it that way since it looks like it won't lose data from what you posted previously.
April 26, 2012 at 12:49 pm
DBCS is not natively supported in SQL Server, only ANSI (CHAR & VARCHAR) as well as Unicode (NCHAR & NVARCHAR). If you want to store DBCS in SQL Server it is recommended you use Unicode-typed columns to store it (my line of thinking originally that you said was not what you wanted)
...if you want to interface with a Unicode-typed column as ANSI you had better have a fool-proof scheme to do that conversion in and out. I am confident a lossless scheme could be devised, but you would need to define how that would work which is what I have been trying to get out of you...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply