1

I am trying to convert a varbinary to varchar but it is causing some unwanted characters to show up, as shown below. Could someone please let me know if there is any best way to do it.

I'm trying the following query

SELECT
    BLOB_CONTENTS, 
    CONVERT(VARCHAR(MAX), [BLOB_CONTENTS]), 
    CONVERT(NVARCHAR(MAX), [BLOB_CONTENTS]) 
FROM 
    [hie_temp].[V500].[CE_BLOB] 
WHERE
    EVENT_ID = '760365'

varbinary code like this : 

0x05028A64F2A9488645100E84053281148A4324080A0412A1209E4C2791CB2208EC7A3F2090C7

I am getting something like this

Šdò©H†E„2ŠC$ ¡ žL'‘Ë"ìz? Ç…ˆÔl8Œ£hÐr-ŒDBy¨O)B„IÑH\CdòA"¤Ò£Ä²)L‚V “…‚‘<¦H©@‰$â!$‡$“é¸ù°A&ˆ Ö˜M–”DŠ‘EåBI6Ý á2ˆœ (•H¥(á„Nœ“H$ÂIR9")•ñ‘’¸€„A)J¶«|xb1ÊÅã‘Ä€ŒLéE£ XÄr
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
Karthik Daibala
  • 11
  • 1
  • 1
  • 5

1 Answers1

3

RTF is not plain text, you need to write a procedure/function to convert RTF to text, see here.

Alternatively you can do it in your programming language, see this post for an example in C#.

Hooman Bahreini
  • 14,480
  • 11
  • 70
  • 137
  • Thanks a lot for those post. My issue over here is there is a blob which is already created on Oracle for RTF files. So i have extracted those blob into SQl server using SSIS as varbinary. Now i want to decode that varbinary to a text. Please note: varbinary starts wit 0x05 and it is not even digits. Please correct me if i missed anything. – Karthik Daibala Jul 06 '18 at 01:08
  • you can try the 1st link (the code project one) above, you might need to convert your varbinary to nvarchar(max) first and then pass it RTF2Text function. – Hooman Bahreini Jul 06 '18 at 01:14
  • what is the code project one? i am sorry , i am new to this. can you be bit more clear? – Karthik Daibala Jul 06 '18 at 01:23
  • i have created function and passed my varbinary to it. but i get the result like below. ȅ撊꧲虈၅萎㈅ᒁ䎊ࠤЊꄒ鸠 – Karthik Daibala Jul 06 '18 at 01:25
  • did you try converting your varbinary to nvarchar(max), and then passing it to RTF2Text? – Hooman Bahreini Jul 06 '18 at 01:27
  • i tried this Select BLOB_CONTENTS , [dbo].[RTF2Text](convert(nvarchar(max), BLOB_CONTENTS)) FROM [hie_temp].[V500].[CE_BLOB] where EVENT_ID = '760365' correct me if i am wrong – Karthik Daibala Jul 06 '18 at 01:29
  • yes, that's what I meant... not sure! you could verify if this method works by creating a sample test.rtf and inserting it in your SQL DB... maybe some format conversion has happened to your varbinary while extracting it from Oracle and copying it into sql. – Hooman Bahreini Jul 06 '18 at 01:34
  • sample file did work. But the blob which i have extracted from oracle are not working. They are giving me some weird characters – Karthik Daibala Jul 06 '18 at 01:53
  • Ah, you have narrowed down the problem ;-) – Hooman Bahreini Jul 06 '18 at 01:55
  • Is there any way that i can check what is format issue and how can i resolve it ? – Karthik Daibala Jul 06 '18 at 01:56
  • I am not sure whats happening there but that's an entirely new question (you might want to ask a new question and explain the steps that you have completed) – Hooman Bahreini Jul 06 '18 at 01:59