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 =3I 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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply