1

I'm runing query like this:

SELECT CAST(CAST(IDAT as varbinary(max)) as varchar(max))

IDAT column type is the "image", query executing successful but some Turkish characters are 'çaÄŸdaÅŸ' like this. it should be 'çağdaş'. I tried this :

SELECT CAST(CAST(IDAT as varbinary(max)) as varchar(max)) COLLATE Turkish_CI_AS

Nothing changed. Does anyone have an idea for this situation?

Improve this question
4
  • 'çaÄŸdaÅŸ' is 9 symbols, while 'çağdaş' is only 6. What is the size of the data in IDAT? Post is as hex value. Do you know the encoding used for the data stored there? Commented Nov 30, 2018 at 7:12
  • Value before casting varchar : 0x68C3BC736579696E20616C692076656C69206E6F746C617220C59F65726D696E2061C49F72C4B120C3B66BC3BC7A20C3A761C49F6461C59F i dont know encoding using for this data. Commented Nov 30, 2018 at 7:35
  • SQL Server already supports Unicode. Don't store text as binary, you don't need it Commented Nov 30, 2018 at 7:51
  • The Unicode types are nchar and nvarchar. Don't use varchar unless you can ensure the text matches the collation. Commented Nov 30, 2018 at 7:58

1 Answer 1

Reset to default
4

This binary value 0x68C3BC736579696E20616C692076656C69206E6F746C617220C59F65726D696E2061C49F72C4B120C3B66BC3BC7A20C3A761C49F6461C59F is UTF-8 encoded, which converted to text is hüseyin ali veli notlar şermin ağrı öküz çağdaş. Unfortunately, SQL Server doesn't support UTF-8 until 2019, so you must decode it yourself. This question explains how to do it.

If you can change the application and store the value in UTF-16 you will be able to cast it to NVARCHAR directly (or just store the data as text).

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The answer to that question is really bad. A simple SQLCLR UDF that with just a single call Encoding.UTF8.GetString(inputBytes.Value) would be faster and far more likely to be correct. This question shows the reverse, generating UTF8 bytes from a string.
Yes, if CLR is an option, it will be much better option. But still, changes in the application would be better. If this text must be available as UTF-8 in the database (why?!) and converted to text in the database (again, why?), then the application could simply store it twice :)
Actually, My only interest is converting this data type to the right string. in this case, @AndreyNikolov solution is successful worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.