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.
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
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)
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
