SQL convert string data in hexadecimal format into string text

Question

I have a table which has a column X. X will be storing large text values in hex format. Now I want to convert hex to raw and validate the data. But when I am using the below query, I am getting only some part of text after running the query not the complete text. Actually original text is very large....

select UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(X)) as a from table name

I also tried the below query, but no use it also extracts the same

decalre @a varchar(max)
select UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(X)) as new  from table name.

Kindly let me know how can i extract or see large text from sql. Sample query may be helpful.

Are you sure you are using Microsoft SQL Server 2008? AFAIK HEXTORAW() is an Oracle DB function...? The following link might help --> blogs.msdn.com/b/sqltips/archive/2008/07/02/… — robnick
– robnick, Commented Feb 6, 2014 at 8:07

robnick · Accepted Answer · 2014-02-06 08:17:44Z

For MS-SQL 2008 the following stored proc will convert a hex string into a varchar(max):

if exists (select * from dbo.sysobjects where name = 'f_hextostr' and xtype = 'FN')
drop function [dbo].[f_hextostr]
GO

CREATE FUNCTION [dbo].[f_hextostr] (@hexstring VARCHAR(max))
RETURNS VARCHAR(max)

AS

begin
 declare @char1 char(1), @char2 char(1), @strlen int, @currpos int, @result varchar(max)
 set @strlen=len(@hexstring)
 set @currpos=1
 set @result=''
 while @currpos<@strlen
  begin
   set @char1=substring(@hexstring,@currpos,1)
   set @char2=substring(@hexstring,@currpos+1,1)
   if (@char1 between '0' and '9' or @char1 between 'A' and 'F')
    and (@char2 between '0' and '9' or @char2 between 'A' and 'F')
    set @result=@result+
     char((ascii(@char1)-case when @char1 between '0' and '9' then 48 else 55 end)*16+
     ascii(@char2)-case when @char2 between '0' and '9' then 48 else 55 end)
   set @currpos = @currpos+2
  end
 return @result
end
GO

To use just do something like:

select dbo.f_hextostr('0x3031323')

or

select dbo.f_hextostr(X) from MyTable

gknicker · Accepted Answer · 2016-09-01 06:22:04Z

2

This can be done in SQL server using built-in conversions via varbinary data type.

-- VARCHAR TO (HEX) VARCHAR
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), 'Help'), 1) -- '0x48656C70'

-- (HEX) VARCHAR TO VARCHAR
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '0x48656c70', 1)) -- 'Help' (requires 0x)
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '48656c70', 2)) -- 'Help' (assumes 0x)

answered Sep 1, 2016 at 6:22

gknicker

5,5982 gold badges29 silver badges44 bronze badges

Comments

Dan Is Fiddling By Firelight · Accepted Answer · 2014-07-15 18:05:34Z

-2

It seems that this works better for me.

if exists (select * from dbo.sysobjects where name = 'HexToStr' and xtype = 'FN')
drop function [dbo].[HexToStr]
GO

CREATE FUNCTION [dbo].[HexToStr] (@hexstring VARCHAR(max))
RETURNS VARCHAR(max)

AS

begin
 return @hexstring
end
GO

edited Jul 15, 2014 at 18:05

Dan Is Fiddling By Firelight

6,07118 gold badges82 silver badges132 bronze badges

answered Jul 15, 2014 at 18:02

user3842105

1

Collectives™ on Stack Overflow

SQL convert string data in hexadecimal format into string text

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related