April 24, 2012 at 5:40 pm
opc.three (4/24/2012)
MisLead (4/24/2012)
Please refer this. Its exactly what i mean-if you still have some timeI could see if you wanted to tear apart ? into two separate bytes so some legacy program could process it without losing it's mind...but I need to know what that legacy program needs to see and the mapping-example you provided is not making sense to me.
Yes, thats what i need,
tear apart ? into two separate bytes so some legacy program could process it without losing it's mind
How do i do that??
April 24, 2012 at 5:42 pm
OK, that sounds more sane. I could have understood the example not being representative in terms of the actual characters used, but having the number of characters not be representative was a bit of a curveball...I think we're getting somewhere, finally.
If you can provide a set of exact conversions from a single 2-byte character to a pair of 1-byte characters I can likely infer the method...or if you have those rules documented I can help with a conversion function in either T-SQL (if feasible and practical) or SQLCLR.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 24, 2012 at 5:44 pm
MisLead (4/24/2012)
opc.three (4/24/2012)
MisLead (4/24/2012)
Please refer this. Its exactly what i mean-if you still have some timeI could see if you wanted to tear apart ? into two separate bytes so some legacy program could process it without losing it's mind...but I need to know what that legacy program needs to see and the mapping-example you provided is not making sense to me.
Yes, thats what i need,
tear apart ? into two separate bytes so some legacy program could process it without losing it's mind
How do i do that??
One way would be to convert the 2-byte character to binary and using bitwise operations isolate the first and second byte, and provide those as separate 8-bit ASCII characters. There are lots of possibilities, but it will depend on the conversion method used as to the best way...still need more info...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 24, 2012 at 6:04 pm
opc.three (4/24/2012)
MisLead (4/24/2012)
opc.three (4/24/2012)
MisLead (4/24/2012)
Please refer this. Its exactly what i mean-if you still have some timeI could see if you wanted to tear apart ? into two separate bytes so some legacy program could process it without losing it's mind...but I need to know what that legacy program needs to see and the mapping-example you provided is not making sense to me.
Yes, thats what i need,
tear apart ? into two separate bytes so some legacy program could process it without losing it's mind
How do i do that??
One way would be to convert the 2-byte character to binary and using bitwise operations isolate the first and second byte, and provide those as separate 8-bit ASCII characters. There are lots of possibilities, but it will depend on the conversion method used as to the best way...still need more info...
I don't have any conversion method preferance. It doesn't really matter as long as i can do it within sql. I have a column containing the unicode character that i need to break apart two ASCII double bytes like you said above.I am new to sql and particularly in conversion and this requirement is kind of immediate. I'd really appreciate if you could input some codes. Thanks
April 25, 2012 at 4:25 pm
MisLead (4/24/2012)
opc.three (4/24/2012)
MisLead (4/24/2012)
opc.three (4/24/2012)
MisLead (4/24/2012)
Please refer this. Its exactly what i mean-if you still have some timeI could see if you wanted to tear apart ? into two separate bytes so some legacy program could process it without losing it's mind...but I need to know what that legacy program needs to see and the mapping-example you provided is not making sense to me.
Yes, thats what i need,
tear apart ? into two separate bytes so some legacy program could process it without losing it's mind
How do i do that??
One way would be to convert the 2-byte character to binary and using bitwise operations isolate the first and second byte, and provide those as separate 8-bit ASCII characters. There are lots of possibilities, but it will depend on the conversion method used as to the best way...still need more info...
I don't have any conversion method preferance. It doesn't really matter as long as i can do it within sql. I have a column containing the unicode character that i need to break apart two ASCII double bytes like you said above.I am new to sql and particularly in conversion and this requirement is kind of immediate. I'd really appreciate if you could input some codes. Thanks
There are many ways to accomplish the task, be it through T-SQL or SQLCLR, and while I empathize with your situation all methods are irrelevant if they do not do what is actually needed...which I am still unclear about. Say we split an NCHAR(1) into a CHAR(2) using binary operations and one half of the result lands on a point in the CHAR code page that is non-existent in the Collation...guess what SQL Server does...it converts it to a question mark because it does not know what else to do with it. What are the rules surrounding the conversion in that case? What is the desired Collation? Before we even get to that point, I am still unclear as to how to split the NCHAR(1), whether it should be simple binary math to get the first 8 bits of the 2 bytes for the first char, and the second 8 bits for the second char...or....? Can you provide a few representative mappings from NCHAR(1) to CHAR(2) so I can see what it is you're looking for?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 4:30 pm
I am still waiting to hear the reason for all this work? What is the case for doing it? What other alternatives are there that would make more sense?
Edit: Correct spelling of hear instead of here.
April 25, 2012 at 4:37 pm
Lynn Pettis (4/25/2012)
I am still waiting to here the reason for all this work? What is the case for doing it? What other alternatives are there that would make more sense?
No doubt, Lynn. It seems like an odd case of a legacy app needing to talk to a newer database platform. I have encountered something similar with a PHP app running on Linux needing to talk to SQL Server 2008 when 2008 first was released and the Linux PHP data access methods had not caught up, but this has far exceeded what I would typically do in a Forum setting...and the more questions that go unanswered (including yours) the less likely I am to continue to offer assistance.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 6:32 pm
opc.three (4/25/2012)
Lynn Pettis (4/25/2012)
I am still waiting to here the reason for all this work? What is the case for doing it? What other alternatives are there that would make more sense?No doubt, Lynn. It seems like an odd case of a legacy app needing to talk to a newer database platform. I have encountered something similar with a PHP app running on Linux needing to talk to SQL Server 2008 when 2008 first was released and the Linux PHP data access methods had not caught up, but this has far exceeded what I would typically do in a Forum setting...and the more questions that go unanswered (including yours) the less likely I am to continue to offer assistance.
opc.three, sorry I was away from internet the whole day today.
Lynn, The requirement is so as “opc.three” put up there- it’s the legacy system problem. The client has a legacy system that does not yet support unicodes. Picture a situation where you are having to store a data that does not only supports ANSI but has UNICODE data coming in. The ultimate goal being to avoid the corruption of data. When I say Unicode its non English characters. So within a regular conversion as not all Unicode as ASCII counterpart, the data loss results into question mark.We don’t that.I have explained this earlier as well. However, what I am trying to do is convert these Unicode non English characters to extended ASCII values so that they are preserved.
April 25, 2012 at 6:53 pm
There are many ways to accomplish the task, be it through T-SQL or SQLCLR, and while I empathize with your situation all methods are irrelevant if they do not do what is actually needed...which I am still unclear about. Say we split an NCHAR(1) into a CHAR(2) using binary operations and one half of the result lands on a point in the CHAR code page that is non-existent in the Collation...guess what SQL Server does...it converts it to a question mark because it does not know what else to do with it. What are the rules surrounding the conversion in that case? What is the desired Collation? Before we even get to that point, I am still unclear as to how to split the NCHAR(1), whether it should be simple binary math to get the first 8 bits of the 2 bytes for the first char, and the second 8 bits for the second char...or....? Can you provide a few representative mappings from NCHAR(1) to CHAR(2) so I can see what it is you're looking for?
NCHAR(1)--------------------------> Convert----------------------------->CHAR(2)
? §(1) Ç(1)
how it can be done? thats what i am trying to find out. And the question is valid because, these chinese/japanese/korean can be represented in double bytes-right?
April 25, 2012 at 7:03 pm
MisLead (4/25/2012)
There are many ways to accomplish the task, be it through T-SQL or SQLCLR, and while I empathize with your situation all methods are irrelevant if they do not do what is actually needed...which I am still unclear about. Say we split an NCHAR(1) into a CHAR(2) using binary operations and one half of the result lands on a point in the CHAR code page that is non-existent in the Collation...guess what SQL Server does...it converts it to a question mark because it does not know what else to do with it. What are the rules surrounding the conversion in that case? What is the desired Collation? Before we even get to that point, I am still unclear as to how to split the NCHAR(1), whether it should be simple binary math to get the first 8 bits of the 2 bytes for the first char, and the second 8 bits for the second char...or....? Can you provide a few representative mappings from NCHAR(1) to CHAR(2) so I can see what it is you're looking for?
NCHAR(1)--------------------------> Convert----------------------------->CHAR(2)
? §(1) Ç(1)
how it can be done? thats what i am trying to find out. And the question is valid because, these chinese/japanese/korean can be represented in double bytes-right?
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?
April 25, 2012 at 10:08 pm
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.
April 25, 2012 at 11:18 pm
Well, good luck. I'd push to store the data as nvarchar instead of trying convert the data back and forth between unicode and extended ascii character codes.
April 26, 2012 at 9:32 am
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?
Re: your example, I am having trouble verifying whether I have identified the correct Asian character...but it could just be a result of my browser having it as UTF-8, and when I paste that into SQL Server Management Studio it is munged. Can you please confirm it as Unicode 0x6F22, i.e. code point 28450?
SELECT UNICODE(N'?'), -- 28450
ASCII('§'), -- 167
ASCII('Ç') -- 199
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2012 at 11:02 am
opc.three (4/26/2012)
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 BINARY
SELECT 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/varbinary, it needs to be varchar (extended ascii)
April 26, 2012 at 11:06 am
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...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply