rc
asked on
Convert HEX to ASCII - TSQL
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.
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.
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
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.
Did you try
SELECT Convert(varchar, 0x+<columnName>)
SELECT Convert(varchar, 0x+<columnName>)
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
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
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?
Can you give a simple example:
What would you expect to see for an input value of CF?
ASKER
Hi Jim,
The query you posted in your first comment is not working. I tried that too..
Here is the input: 35323932633262653030303030 3030303061 3064366336 3832333336 30303032
Output should be: 5292c2be000000000a0d6c6823 360002
The query you posted in your first comment is not working. I tried that too..
Here is the input: 35323932633262653030303030
Output should be: 5292c2be000000000a0d6c6823
So you're actually trying to convert ASCII to Hex, not Hex to ASCII?
You can create a function for converting hex to ASCII:
Then you can use that in the query to convert the value from each record:
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
Then you can use that in the query to convert the value from each record:
update TheTable
set TextValue = dbo.HexToAscii(HexValue)
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW, you're converting a decimal number to a hexadecimal number. ASCII doesn't come anywhere near this entire concept.
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.
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.
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:
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.
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.
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.
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.
ASKER
Thank you everyone for spending time on my req. I really appreciate your effort. Thanks a lot.
ASKER
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 ?