Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert HEX to ASCII - TSQL

Posted on 2013-12-09
16
Medium Priority
?
10,359 Views
Last Modified: 2013-12-15
Hello Experts,

I am trying to write a query to convert Hexadecimal record into ASCII. I have this below query, but I want to apply this query to a table to populate a new field.

declare @v varchar(200), @sql nvarchar(MAX), @ch varchar(200)
select @v = '35323139636430323030303030303030306130393031623332333339303030323b30313631363132363030303230303431'
select @sql = 'SELECT @ch = convert(varchar, 0x' + @v + ')'
EXEC sp_executesql @sql, N'@ch varchar(30) OUTPUT', @ch OUTPUT
SELECT @ch AS ConvertedToASCII

Open in new window


I have a table with few columns. One column is basically a varchar(200) and it has hexadecimal values. I want to populate another column with ASCII values in the same table based on the hexadecimal column.

Can someone please help me with the query.
0
Comment
Question by:ravichand-sql
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +4
16 Comments
 

Author Comment

by:ravichand-sql
ID: 39707709
I have tried the below solution..

SELECT convert(varchar, 0x'Hex value')

Open in new window


But this is working only for a single record. How to modify the above query, if I want to use a column instead ?
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39708941
Did you try
SELECT Convert(varchar, 0x+<columnName>)
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39708945
What about ...
Update MyTable set strField = convert(varchar(64), hexField,0);

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6bfe99c8-c662-44bf-ae10-74b4a00d4da7/hex-to-string?forum=transactsql
for more options / discussion
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 15

Expert Comment

by:JimFive
ID: 39708978
I think I don't understand the question.  In your example, everything is already ASCII, there are no numeric types referenced.

Can you give a simple example:
What would you expect to see for an input value of CF?
0
 

Author Comment

by:ravichand-sql
ID: 39709035
Hi Jim,

The query you posted in your first comment is not working. I tried that too..

Here is the input: 3532393263326265303030303030303030613064366336383233333630303032

Output should be: 5292c2be000000000a0d6c6823360002
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39709152
So you're actually trying to convert ASCII to Hex, not Hex to ASCII?
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 39709560
You can create a function for converting hex to ASCII:

create function HexToAscii(@hex varchar(max))
returns varchar(max)
as
begin
	declare @result varchar(max) = '', @i int = 1, @c int
	while @i < len(@hex) begin
		set @c = (charindex(substring(@hex,@i,1), '0123456789abcdef') - 1) * 16 + charindex(substring(@hex,@i+1,1), '0123456789abcdef') - 1
		set @result = @result + char(@c)
		set @i = @i + 2
	end
	return @result
end

Open in new window


Then you can use that in the query to convert the value from each record:

update TheTable
set TextValue = dbo.HexToAscii(HexValue)

Open in new window

0
 
LVL 15

Expert Comment

by:JimFive
ID: 39709632
For this solution you need a table called Numbers with a column called Number that contains the numbers from 1 to the max length of your conversion field.

This works for a single value.  For a table you will need to adjust the select statement to include your key field as well as to correlate the subquery.

DECLARE @x as varchar(MAX)
Set @x = '35323139636430323030303030303030306130393031623332333339303030323b30313631363132363030303230303431'

SELECT @x, (select CHAR(LEFT(SUBSTRING(@x,Number,2),1)*16+RIGHT(Substring(@X,Number,2),1))
                   FROM Numbers
                   WHERE number > 0 and number < Len(@x) and number%2 = 1
                   ORDER BY Number
                   FOR XML PATH('')) fixed

Open in new window

0
 
LVL 15

Accepted Solution

by:
dbbishop earned 668 total points
ID: 39709724
See this solution

Use:
UPDATE myTable
SET newColumn = dbo.HexToStr(CONVERT(NVARCHAR(MAX), myColumn, 2))
0
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 668 total points
ID: 39709836
I had a little more time to work on this so this will return a recordset, you'll need to fill in your actual table and column names.

SELECT ST1.Key, ST1.VAL, (select CHAR(LEFT(SUBSTRING(ST2.VAL,Number,2),1)*16+RIGHT(Substring(ST2.VAL,Number,2),1))
                   FROM SourceTable ST2, Numbers
                   WHERE ST2.Key = ST1.Key
                   AND number > 0 and number < Len(ST2.VAL) and number%2 = 1
                   ORDER BY Number
                   FOR XML PATH('')) fixed
FROM SourceTable ST1

Open in new window


To use in an update Statement
UPDATE SourceTable
SET asciiValue = (select CHAR(LEFT(SUBSTRING(ST2.VAL,Number,2),1)*16+RIGHT(Substring(ST2.VAL,Number,2),1))
                   FROM SourceTable ST2, Numbers
                   WHERE ST2.Key = SourceTable.Key
                   AND number > 0 and number < Len(ST2.VAL) and number%2 = 1
                   ORDER BY Number
                   FOR XML PATH(''))

Open in new window

0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 664 total points
ID: 39710376
just try

declare @sql varchar(max)
 select @sql = '3532393263326265303030303030303030613064366336383233333630303032'

 SELECT convert(varchar(max),  convert(varbinary(max),@sql,2) )

--result
--5292c2be000000000a0d6c6823360002
------------------------------------------------------------------------------------------------------------
---or just directly

SELECT convert(varchar(max),  convert(varbinary(max),'3532393263326265303030303030303030613064366336383233333630303032',2) )

--result
--5292c2be000000000a0d6c6823360002

Open in new window


<I want to populate another column with ASCII values in the same table based on the hexadecimal column.>

just as per above post

update yourtable
set strCol=convert(varchar(max),  convert(varbinary(max),hexCol,2) )
where somecol='something'
0
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 39715415
BTW, you're converting a decimal number to a hexadecimal number.  ASCII doesn't come anywhere near this entire concept.
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 39715443
cpkilekofp, the input string is the hexadecimal representation of the ASCII characters that form the output string.

The input just looks like it's decimal because it happens to lack any components over 9, and the output is not a hexadecimal number, it's a string that just happens to be the hexadecimal representation of a number.
0
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 39717725
This post is not truly relevant to the topic at hand and may be deleted by adminstrative personnel with prejudice on my part if it is deemed too far off-topic.

GreenGhost,

You said:
cpkilekofp, the input string is the hexadecimal representation of the ASCII characters that form the output string.

That is clearly not true.  If it were true, each ASCII character in the input string would be represented by two hexadecimal digits in the output string, making it twice the size of the input string.  

On the other hand, an ASCII string containing a decimal number will convert to a SHORTER ASCII string if the output string is considered to be a hexadecimal representation of the decimal number in the original string unless it is padded with zeroes.  

This is easily demonstrated:  decimal and hex 0 through 9 are the same length; decimal 10 is length 2 while the corresponding hex value A is of length 1; decimal 100 of length 3 is longer than hex 64; I'll skip a formal proof here, but the decimal length will always be greater than or equal to the hex length.

The example given of a correct output string is considerably shorter than the input.  Therefore, it cannot be a conversion of its ASCII characters to a hexadecimal representation, but must more reasonable be assumed to be a conversion of a decimal number to its hexadecimal representation.
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 39718054
cpkilekofp, you have it backwards.

The input is the hexadecimal representation of the ASCII codes for the characters in the output, not the other way around.

It's easy to see that this is the correct way to convert the data if you actually try to do the conversion of the example data.
0
 

Author Closing Comment

by:ravichand-sql
ID: 39720066
Thank you everyone for spending time on my req. I really appreciate your effort. Thanks a lot.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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