October 28, 2010 at 5:23 am
I have a table Table1 which has a Col called "Msg" datatype image<binary>. Msg alreay has the plain text or RTF text as a image datatype (binary)If I execute the following query "Select Msg from Table1 where id =3" then this query is returning the following ASCII/Binary data.Msg = "0x7B5C727466315C616E73695C616E7369637067313235325C64656666305C6465666C616E67313033337B5C666F6E7474626C7B5C66305C6673776973735C66707271325C66636861727365743020417269616C3B7D7B5C66315C6673776973735C66707271325C666368617273657430204D6963726F736F667420"Can any body tell me how can I convert the above binary data to plain text from my query?Thanks for any reply.
October 28, 2010 at 5:53 am
Try this:
Select cast(Msg as varchar(max)) from Table1 where id =3
I am not sure whether this will handle RTF text.
October 28, 2010 at 12:52 pm
You would need an RTF to text converter routine to switch the data-types/ If one creates an RTF with some contents and then opens the file using Notepad, you can see what will happen:
WYSIWYG appearance where font names are indicated:
Start of RTF
Arial 10 BOLD Italics
Times New Roman BOLD italics
End of RTF
Actual file contents:
{\rtf1\adeflang1025\ansi\ansicpg1252\uc1\adeff0\deff0\stshfdbch0\stshfloch0\stshfhich0\stshfbi0\deflang1033\deflangfe1033{\fonttbl{\f0\froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\f1\fswiss\fcharset0\fprq2{\*\panose 020b0604020202020204}Arial;}{\f198\froman\fcharset238\fprq2 Times New Roman CE;}
{\f199\froman\fcharset204\fprq2 Times New Roman Cyr;}{\f201\froman\fcharset161\fprq2 Times New Roman Greek;}{\f202\froman\fcharset162\fprq2 Times New Roman Tur;}{\f203\fbidi \froman\fcharset177\fprq2 Times New Roman (Hebrew);}
{\f204\fbidi \froman\fcharset178\fprq2 Times New Roman (Arabic);}{\f205\froman\fcharset186\fprq2 Times New Roman Baltic;}{\f206\froman\fcharset163\fprq2 Times New Roman (Vietnamese);}{\f208\fswiss\fcharset238\fprq2 Arial CE;}
{\f209\fswiss\fcharset204\fprq2 Arial Cyr;}{\f211\fswiss\fcharset161\fprq2 Arial Greek;}{\f212\fswiss\fcharset162\fprq2 Arial Tur;}{\f213\fbidi \fswiss\fcharset177\fprq2 Arial (Hebrew);}{\f214\fbidi \fswiss\fcharset178\fprq2 Arial (Arabic);}
{\f215\fswiss\fcharset186\fprq2 Arial Baltic;}{\f216\fswiss\fcharset163\fprq2 Arial (Vietnamese);}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;
\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;\red128\green128\blue128;\red192\green192\blue192;\red68\green68\blue68;}
{\stylesheet{\ql \li0\ri0\widctlpar\wrapdefault\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af0\afs24\alang1025 \ltrch\fcs0 \fs24\lang1033\langfe1033\cgrid\langnp1033\langfenp1033 \snext0 Normal;}{\*\cs10 \additive \ssemihidden
Default Paragraph Font;}{\*\ts11\tsrowd\trftsWidthB3\trpaddl108\trpaddr108\trpaddfl3\trpaddft3\trpaddfb3\trpaddfr3\tblind0\tblindtype3\tscellwidthfts0\tsvertalt\tsbrdrt\tsbrdrl\tsbrdrb\tsbrdrr\tsbrdrdgl\tsbrdrdgr\tsbrdrh\tsbrdrv
\ql \li0\ri0\widctlpar\wrapdefault\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af0\afs20 \ltrch\fcs0 \fs20\lang1024\langfe1024\cgrid\langnp1024\langfenp1024 \snext11 \ssemihidden Normal Table;}}
{\*\latentstyles\lsdstimax156\lsdlockeddef0}{\*\pgptbl {\pgp\ipgp0\itap0\li0\ri0\sb0\sa0}{\pgp\ipgp0\itap0\li0\ri0\sb0\sa0}}{\*\rsidtbl \rsid731515\rsid748123\rsid1400383\rsid1927666\rsid2580561\rsid3999558\rsid4735566\rsid5717024\rsid5726032\rsid6122162
\rsid6912825\rsid7820288\rsid8401329\rsid10633446\rsid11013585\rsid11169430\rsid13514150\rsid14237291\rsid15482305\rsid16008451\rsid16196535\rsid16457759\rsid16465344}{\*\generator Microsoft Word 11.0.0000;}{\info{\title Start of RTF}{\author fedecx}
{\operator fedecx}{\creatim\yr2010\mo10\dy28\hr13\min47}{\revtim\yr2010\mo10\dy28\hr13\min47}{\version2}{\edmins2}{ofpages1}{ofwords11}{ofchars67}{\*\company Exelon}{ofcharsws77}{\vern24615}{\*\password 00000000}}{\*\xmlnstbl {\xmlns1 http://schem
as.microsoft.com/office/word/2003/wordml}}\paperw12240\paperh15840\margl1800\margr1800\margt1440\margb1440\gutter0\ltrsect
\widowctrl\ftnbj\aenddoc\donotembedsysfont0\donotembedlingdata0\grfdocevents0\validatexml1\showplaceholdtext0\ignoremixedcontent0\saveinvalidxml0\showxmlerrors1oxlattoyen\expshrtnoultrlspc\dntblnsbdbospaceforul\formshade\horzdoc\dgmargin\dghspace180
\dgvspace180\dghorigin1800\dgvorigin1440\dghshow1\dgvshow1
\jexpand\viewkind1\viewscale100\pgbrdrhead\pgbrdrfoot\splytwnine\ftnlytwnine\htmautspolnhtadjtbl\useltbaln\alntblind\lytcalctblwd\lyttblrtgr\lnbrkruleobrkwrptbl\snaptogridincell\allowfieldendsel\wrppunct
\asianbrkrule\rsidroot5726032ewtblstyrulsogrowautofit \fet0{\*\wgrffmtfilter 013f}\ilfomacatclnup0\ltrpar \sectd \ltrsect\linex0\endnhere\sectlinegrid360\sectdefaultcl\sftnbj {\*\pnseclvl1\pnucrm\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl2
\pnucltr\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl3\pndec\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl4\pnlcltr\pnstart1\pnindent720\pnhang {\pntxta )}}{\*\pnseclvl5\pndec\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl6
\pnlcltr\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl7\pnlcrm\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl8\pnlcltr\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl9\pnlcrm\pnstart1\pnindent720\pnhang
{\pntxtb (}{\pntxta )}}\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0\pararsid5726032 \rtlch\fcs1 \af0\afs24\alang1025 \ltrch\fcs0 \fs24\lang1033\langfe1033\cgrid\langnp1033\langfenp1033 {
\rtlch\fcs1 \af1\afs20 \ltrch\fcs0 \f1\fs20\cf17\insrsid5726032 Start of RTF
\par
\par Arial 10 }{\rtlch\fcs1 \af1\afs20 \ltrch\fcs0 \b\f1\fs20\cf17\insrsid5726032\charrsid5726032 BOLD}{\rtlch\fcs1 \af1\afs20 \ltrch\fcs0 \b\f1\fs20\cf17\insrsid5726032 }{\rtlch\fcs1 \af1\afs20 \ltrch\fcs0 \i\f1\fs20\cf17\insrsid5726032\charrsid5726032
Italics}{\rtlch\fcs1 \af1\afs20 \ltrch\fcs0 \i\f1\fs20\cf17\insrsid5726032
\par
\par }{\rtlch\fcs1 \af0\afs20 \ltrch\fcs0 \fs20\cf17\insrsid5726032\charrsid5726032 Times New Roman }{\rtlch\fcs1 \af0\afs20 \ltrch\fcs0 \b\fs20\cf17\insrsid5726032\charrsid5726032 BOLD}{\rtlch\fcs1 \af0\afs20 \ltrch\fcs0 \i\fs20\cf17\insrsid5726032 italics}{
\rtlch\fcs1 \af0\afs20 \ltrch\fcs0 \i\fs20\cf17\insrsid5726032\charrsid5726032
\par }\pard \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 {\rtlch\fcs1 \af0 \ltrch\fcs0 \insrsid14237291
\par }{\rtlch\fcs1 \af0 \ltrch\fcs0 \insrsid5726032 End of RTF
\par }}
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy