Convert Blob to text in SQL Server Query

nuno.silva

Hi,

I'm having trouble to in converting a Blob field not compressed, to text in SQL server query. I can extract the text my problem is the accents.

a few Examples:
I store the text "Bicicleta estática para toda la familia." and I get in query "Bicicleta est tica para toda la familia."

I store the text "Cinta de correr diseñada para uso doméstico regular. Sistema de medición de pulso por contacto." and I get in query "Cinta de correr dise¤ada para uso dom‚stico regular. Sistema de medici¢n de pulso por contacto."

The Language It's Spanish and I have the server with the Modern_Spanish_CI_AS collation and database is SQL_Latin1_General_CP1_CI_AI.

The query that I'm doing is:
SELECT I.No_, CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),I.[Short Description]))
But if I use the two below I get the same result
SELECT I.No_, CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),I.[Short Description])) COLLATE SQL_Latin1_General_CP1_CI_AI
SELECT I.No_, CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),I.[Short Description])) COLLATE SQL_Latin1_General_CP1_CI_AI


But if I go to query analyzer and run

SELECT CONVERT(BINARY(100), 'Cinta de correr diseñada para uso doméstico regular. Sistema de medición de pulso por contacto.') AS [text to binary]

the output is 0x43696E746120646520636F727265722064697365F161646120706172612075736F20646F6DE9737469636F20726567756C61722E2053697374656D61206465206D6564696369F36E2064652070756C736F20706F7220636F6E746163746F2E0000000000

then if I convert back to text the last output

SELECT CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX), 0x43696E746120646520636F727265722064697365F161646120706172612075736F20646F6DE9737469636F20726567756C61722E2053697374656D61206465206D6564696369F36E2064652070756C736F20706F7220636F6E746163746F2E0000000000)) AS 'binary to text'

then the output is correct
Cinta de correr diseñada para uso doméstico regular. Sistema de medición de pulso por contacto.

Anyone already encountered a situation like this?

thanks in advance

vremeni4

Hi,

I think the problem is caused by the Database collation.

Modern_Spanish_CI_AS collation and
database is SQL_Latin1_General_CP1_CI_AI

SQL_Latin1_General = means Latin characters will be used,
CP1 = stand for code page 1, which is 1252 page in NAV
CI = case insensitive
AI= accent insensitive.

As database is AI, SQL query does not returns accents on the characters.

Please be aware there is a bug in NAV 2013 R2 so you cannot just change the collation.

I hope this helps.

Thanks.

nuno.silva

vremeni4 wrote:

Hi,

I think the problem is caused by the Database collation.

Modern_Spanish_CI_AS collation and
database is SQL_Latin1_General_CP1_CI_AI

SQL_Latin1_General = means Latin characters will be used,
CP1 = stand for code page 1, which is 1252 page in NAV
CI = case insensitive
AI= accent insensitive.

As database is AI, SQL query does not returns accents on the characters.

I thought that the Collation will only change the behavior of the sort of your queries an the search of data, I never heard that remove characters from the data that you have stored in your database. Anyone can confirm or not vremeni4 reply?