?
Solved

Hex to Ascii Help

Posted on 2008-11-12
9
Medium Priority
?
817 Views
Last Modified: 2012-05-05
Hi.  I am trying to convert the contents of a field to ASCII.

The Field is of Image type and when viewed through the Gui is says Binary Data but when retreived via a select statement this is what is returned, HEX .

0x0015C5EF900800144F6AA35A080045000089AE464000400669710A000D050A0001B3A16761AF31A889D7FFB57FD25018C1E88AA300000105010100640401000000000000006100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005783000000000000E577

If I put this in a Hex to ASCII converter I get.

?????????Oj?Z??E????F@?@?iq?????????ga?1???????P????????????d?????????a???????????????????????????????????????????????????????????????????????W????????w

This the correct data (I cannot post an example with readable information)

How do I use a CLR or the like to convert this date in a SQL select statement?

Thanks

Matthew
0
Comment
Question by:Matthew_Lowe
  • 7
  • 2
9 Comments
 
LVL 15

Assisted Solution

by:quincydude
quincydude earned 500 total points
ID: 22946542
0
 

Author Comment

by:Matthew_Lowe
ID: 22946828
I have been looking at this and it kind of works but it only seems to convert the first 30 chars (I have increased all defs to MAX)

This would be great if it could convert the whole string (see above) .

I just cannot seem to work out how to do that. Any ideas?

Matthew
0
 
LVL 15

Assisted Solution

by:quincydude
quincydude earned 500 total points
ID: 22946920
you see that bit in the code with
N'@ch varchar(30) OUTPUT    ?

could this be the cause?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:Matthew_Lowe
ID: 22947032
The code I am running is below. I had already changed it but there still seems to be a limit.

Thanks

Matthew  

declare @v varchar(8000), @sql nvarchar(4000), @ch varchar(8000)
select @v = '0x0015C5EF900800144F6AA35A080045000109AE3E4000400668F90A000D050A0001B3A17761AFF08CE0C0FF862F615018C1E8DE060000010501010064020100000000000000E1000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000011D82FBEFAA0000000000005783000000000000000F000000000000000000000000000000560000715753505F506F7274616C5F4F5554004A4D53436F7272656C6174696F6E49443D2749443A37353339613A31316438326662656661653A376239633A37663030303030313A31316438643862613333323A32383666653631316438643635353835656536302700'
select @sql = 'SELECT @ch = convert(varchar, ' +@v + ')'
EXEC sp_executesql @sql, N'@ch varchar(8000) OUTPUT', @ch OUTPUT
SELECT @ch,
upper(substring(master.sys.fn_varbintohexstr(convert(varbinary, @ch)),
3, 2*len(@ch))
0
 

Author Comment

by:Matthew_Lowe
ID: 22947062
I have solved the reading all the values (see below). Now its sees to not like some special chars and stops. If I put a string in that only contains valid ASCII Chars its OK.

Is there a way to error check etc on this?

Mattew

declare @v varchar(8000), @sql nvarchar(4000), @ch varchar(8000)
select @v = '0x000D050A0001B3A17761AFF08CE0C0FF862F615018C1E8DE060000010501010064020100000000000000E1000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000011D82FBEFAA0000000000005783000000000000000F000000000000000000000000000000560000715753505F506F7274616C5F4F5554004A4D53436F7272656C6174696F6E49443D2749443A37353339613A31316438326662656661653A376239633A37663030303030313A31316438643862613333323A32383666653631316438643635353835656536302700'
select @sql = 'SELECT @ch = convert(varchar(MAX), ' +@v + ')'
EXEC sp_executesql @sql, N'@ch varchar(8000) OUTPUT', @ch OUTPUT
SELECT @ch,
upper(substring(master.sys.fn_varbintohexstr(convert(varbinary(MAX), @ch)),
3, 2*len(@ch)))
0
 

Author Comment

by:Matthew_Lowe
ID: 22947100
00 is the issue. If I take out all the 00 from the HEX its OK.

I now need to find a way to Test for 00 and remove or replace with another char.

Thanks

Matthew
0
 

Accepted Solution

by:
Matthew_Lowe earned 0 total points
ID: 22947146
Doing a Regex replace for 00 with 3f (?) works well.

Thanks for the help.

Matthew
0
 

Author Comment

by:Matthew_Lowe
ID: 22953617
All fixed now
0
 

Author Comment

by:Matthew_Lowe
ID: 22953619
All fixed now
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question