convert BLOB to text in sql

Question

I have a field in my database table with data type as BLOB.How can I view the content as text/string using a SELECT query in SQL. the content's MIMETYPE is 'text/xml charset=UTF8'

I tried with this, I'm not sure if im right with the syntax

SELECT 
CAST((SELECT column FROM myTable WHERE ID='56')AS CHAR(10000) CHARACTER SET utf8)

and also

SELECT 
CONVERT(VARCHAR(max), CAST((SELECT column FROM myTable WHERE ID='56') 
as binary)) from BIZDOCCONTENT

many Thanks

Solution 1

Try:

SELECT CONVERT(object USING utf8)
FROM tablename

Solution 2

Try this query -

SELECT CONVERT(column USING utf8) FROM myTable WHERE ID=56

Solution 3

I had the same problem - I just changed the field type in phpmyadmin! And what i see :

ALTER TABLE pages CHANGE content content TEXT NULL DEFAULT NULL
('content' - my field which was in BLOB-type)

Comments

[shockwave]

I tried this but, I get an error saying right parenthesis missing. The syntax is proper. What mite be the error?

[shockwave]

what do I give in place of object?

[shockwave]

I am not using PHP! Is there anything I can do about this in java/ sql. Please help!

[Devart]

Yes, syntax is correct, query query should work. Try to run this command in another MySQL client, e.g. in command-line client.

[urbanspr1nter]

Should just be the column containing your blog. :)