Page 1 of 1

Storing text in binary or visa versa

Posted: Thu Aug 14, 2008 2:03 am
by alex.barylski
I need to store some binary data in an UTF text field...and the field needs to stay a TEXT field because I need FULLTEXT support.

Is it maybe possible to reverse this process and store the TEXT as BINARY data and instead of relying FULLTEXT I could implement a stored procedure which would go through the BLOB fields convert the binary data to TEXT and then perform a FULLTEXT search?

Can you access FULLTEXT from within stored procedures?

Can you convert one to the other without data loss?

I can see converting TEXT to BINARY no problems...but BINARY to TEXT might because of character encoding. If the BINARY BYTE is beyond ASCII (which is what I understand UTF supports natively?) then I assume it would become two bytes in order to be represented in UTF and when that data was converted back again...the one BYTE would now be two and the binary data would be scrambled...

I'm wondering if I could maybe use pack/unpack to convert a binary data into an array, serialize it and store it that way???

Re: Storing text in binary or visa versa

Posted: Thu Aug 14, 2008 3:12 am
by onion2k
My first thought for storing binary data in a text column would be to base64 encode it before it's inserted.

Re: Storing text in binary or visa versa

Posted: Thu Aug 14, 2008 1:16 pm
by dml
Hockey wrote: I can see converting TEXT to BINARY no problems...but BINARY to TEXT might because of character encoding. If the BINARY BYTE is beyond ASCII (which is what I understand UTF supports natively?) then I assume it would become two bytes in order to be represented in UTF and when that data was converted back again...the one BYTE would now be two and the binary data would be scrambled...
So to avoid the scrambing, you specify the encoding when you're converting from binary into text.

Code: Select all

SET @binary_value = convert(@utf8_value USING BINARY);
SET @converted_back = convert(@binary_value USING utf8);
But why are you reluctant to use a TEXT field for storing textual data?