Unicode (Hexadecimal) to varchar conversion

Question

Here is the problem, the system I use can create a saved search which basically generates a 'Where' clause and saves it in the database as type Image .. I am trying to convert the image entry to a readable format, it is saved in 2 forms (Unicode and non unicode) now I can get the non unicode entries fine and they display correctly, however when I try and convert the Unicode entries it does not display correctly an example of what I am trying to convert is

0x01000000FFFEFF00FFFEFF0001050000FFFEFF05510075006F007400650006000000060000000100000001000000000000000000000080000000000000FD00000000000000000700000001050000FFFEFF254E006F0072006700720065006E005F00470072006F007500700020004900640020003D00200030007800300030003000300030003000300030003000300030003000300030003200330004000000060000000100000002000000000000000000002380000000000022B00000000000000001000000FFFEFF5454005400490044005F003400330034005200390032003300370020005F005F00530051004C005F00440045004C0049004D005F005F002000510075006F00740065005F002E004E006F0072006700720065006E005F00470072006F00750070005F004900640020003D00200054005400490044005F00340033003400520039003200330037002E004E006F0072006700720065006E005F00470072006F00750070005F00490064000000000001050000FFFEFF0C45006D0070006C006F0079006500650020003D0020003F0004000000060000000100000002000000000000000000000080000000000001030101000000000001000000FFFEFFA754005400490044005F00320035005200310032003400350034002E0054005400490044005F0032003300520038003600340052003100320034003500340020005F005F00530051004C005F00440045004C0049004D005F005F002000510075006F00740065005F002E0053006800690070005F0054006F005F0043006F006D00700061006E0079005F004900640020003D00200054005400490044005F00320035005200310032003400350034002E0043006F006D00700061006E0079005F0049006400200041004E004400200054005400490044005F00320035005200310032003400350034002E004100630063006F0075006E0074005F004D0061006E0061006700650072005F004900640020003D00200054005400490044005F003200330052003800360034005200310032003400350034002E0045006D0070006C006F007900650065005F00490064000000000001050000FFFEFF0C45006D0070006C006F0079006500650020003D0020003F0004000000060000000100000002000000000000000000000080000000000001030101000000000001000000FFFEFFB354005400490044005F00320035005200310032003400350034002E0054005400490044005F00320033005200380037003000390052003100320034003500340020005F005F00530051004C005F00440045004C0049004D005F005F002000510075006F00740065005F002E0053006800690070005F0054006F005F0043006F006D00700061006E0079005F004900640020003D00200054005400490044005F00320035005200310032003400350034002E0043006F006D00700061006E0079005F0049006400200041004E004400200054005400490044005F00320035005200310032003400350034002E0049006E007400650072006E0061006C005F0043006F006D006D00650072006300690061006C005F00530061006C00650073005F004900640020003D00200054005400490044005F0032003300520038003700300039005200310032003400350034002E0045006D0070006C006F007900650065005F00490064000000000001050000FFFEFF13510075006F0074006500200054006F00740061006C0020003E003D00200032003000300030000400000006000000010000000200000000000000000000008000000000001847010101080000000000000000409F400000000001000000FFFEFF15510075006F00740065005F0020005F005F00530051004C005F00440045004C0049004D005F005F0020000000000001050000FFFEFF1F46006F006C006C006F007700200055007000200053007400610074007500730020003D00200054006F00200046006F006C006C006F007700200055007000040000000600000001000000020000000000000000000000800000000000392F0000000000000001000000FFFEFF15510075006F00740065005F0020005F005F00530051004C005F00440045004C0049004D005F005F0020000200000001050000FFFEFF1F46006F006C006C006F007700200055007000200053007400610074007500730020003D00200049006E00200046006F006C006C006F007700200055007000050000000600000001000000020000000000000000000000800000000000392F0000000000000001000000FFFEFF15510075006F00740065005F0020005F005F00530051004C005F00440045004C0049004D005F005F0020000000000001050000FFFEFF1F46006F006C006C006F007700200055007000200053007400610074007500730020003D0020004E006F00200046006F006C006C006F007700200055007000050000000600000001000000020000000000000000000000800000000000392F0000000000000001000000FFFEFF15510075006F00740065005F0020005F005F00530051004C005F00440045004C0049004D005F005F0020000000000001050000FFFEFF20510075006F0074006500200046006F006C006C006F00770020004500780070006900720065007300200049006E002000440061007900730020003E002000310004000000060000000100000002000000000000000000000080000000000039DD0000000000000001000000FFFEFF4854005400490044005F003700310035004C003100340034003600330020005F005F00530051004C005F00440045004C0049004D005F005F002000510075006F00740065005F002E00510075006F00740065005F005F004900640020003D00200054005400490044005F003700310035004C00310034003400360033002E00510075006F00740065005F005F00490064000000000001050000FFFEFF11510075006F00740065005F0020004900640020004E006F007400200049006E00200004000000060000000100000002000000000000000000000080000000000018F5000000040000000100000000000000000000000000000001000000FFFEFF15510075006F00740065005F0020005F005F00530051004C005F00440045004C0049004D005F005F0020000000000003000000010000000000000000002F9680000000000018F5000000000000000000000000

the SQL code I am using to convert is the following

with saved_lookups_cte as
(select
table_id,
table_name,
saved_lookups_id,
saved_lookup_name,    
case
  when charindex('WhereDelim',query_text) > 0 then 
    substring(query_text,len(query_text) - charindex(reverse('WhereDelim'),reverse(query_text)) + 3,4000)
  else query_text
end query_text
, sql_tree_binary
from (select
  table_id,
  table_name,
  saved_lookups_id,
  saved_lookup_name,
  case isUnicode
    when 1 then [Production_ED].dbo.[RemoveNonASCII] (cast(cast(substring(sql_tree_binary, startIndex, queryLength * 2) as nvarchar(max))as varchar(max)))
    else cast(substring(sql_tree_binary, startIndex, queryLength) as varchar(8000))
  end query_text
 , sql_tree_binary
from (
    select
        saved_lookup_name, 
        case 
            when substring(sql_tree_binary,1,5) = 0x0100000000 then 0
            when substring(sql_tree_binary,1,5) = 0x01000000FF then 1
        end isUnicode,
        cast(case 
            when substring(sql_tree_binary,1,5) = 0x0100000000 then 
                case 
                    when substring(sql_tree_binary,6,1) = 0xFF then substring(sql_tree_binary,8,1) + substring(sql_tree_binary,7,1)
                    else substring(sql_tree_binary,6,1)
                end
            when substring(sql_tree_binary,1,5) = 0x01000000FF then 
                case 
                    when substring(sql_tree_binary,12,1) = 0xFF then substring(sql_tree_binary,14,1) + substring(sql_tree_binary,13,1)
                    else substring(sql_tree_binary,12,1)
                end
        end as int) queryLength,
        case 
            when substring(sql_tree_binary,1,5) = 0x0100000000 then 
                case 
                    when substring(sql_tree_binary,6,1) = 0xFF then 9
                    else 7
                end
            when substring(sql_tree_binary,1,5) = 0x01000000FF then 
                case 
                    when substring(sql_tree_binary,12,1) = 0xFF then 15
                    else 13
                end
        end    startIndex,    
        s.table_id,
        t.table_name,
        s.saved_lookups_id,   
        s.sql_tree_binary
    from 
      Production_Ed.dbo.Saved_Lookups s inner join 
      tables t on s.table_id = t.tables_id
    -- where substring(saved_lookups_id,1,1) <> 0x00
   where saved_lookup_name = 'DE Quotes Open Account Manager >=2000' 
) x
) y

)
select * from saved_lookups_cte

The part of the query that does the conversion is

 when 1 then [Production_ED].dbo.[RemoveNonASCII] 
(cast(cast(substring(sql_tree_binary, startIndex, queryLength * 2) as 
 nvarchar(max))as varchar(max)))

now this actually returns nothing, however if I set the startIndex to 17 and change the queryLength to 3000 * 2 (unicode byte = 2) I get

??Quote?????Norgren_Group Id = 0x0000000000000023???A??????????????? ????????????? ??????????????????????? ? ??????????????????????????????A?????????? ? ???A??Aa??TTID_25R12454.TTID_23R864R12454 __SQ

Returned, although I can read parts, it is not the full query ... yet even if I extend the length I get no more.

Any help would be greatly appreciated.

Code for the RemoveNonASCII function:

ALTER FUNCTION [dbo].[RemoveNonASCII] 
(
@nstring nvarchar(max)
)
RETURNS varchar(max)
AS
BEGIN

DECLARE @Result varchar(max)
SET @Result = ''

DECLARE @nchar nvarchar(1)
DECLARE @position int

SET @position = 1
WHILE @position <= LEN(@nstring)
BEGIN
    SET @nchar = SUBSTRING(@nstring, @position, 1)
    IF ASCII(@nchar) between 32 and 255
        SET @Result = @Result + @nchar
    SET @position = @position + 1
END

RETURN @Result

END

GO

Tag the dbms you're using. (That query is not ANSI SQL compliant.) — jarlh, Nov 16 '17 at 10:09
Can you post the code that makes up the `RemoveNonASCII` function? — iamdave, Nov 16 '17 at 10:39
@iamdave I have added it, I did notice that i had restricted the characters returned to 255 in the function which I have now changed and it does now give me more .. however there are still bits that are not quite right — David Waldron, Nov 16 '17 at 10:49

score 0 · Accepted Answer · answered Nov 16 '17 at 14:57

Ok, so I found the issue, inside the hex string there were null values, which caused the rest of the returned varchar string to become null .. So I just changed the function to:

AS
BEGIN

DECLARE @Result varchar(max)
SET @Result = ''

DECLARE @nchar nvarchar(1)
DECLARE @position int

SET @position = 1
WHILE @position <= LEN(@nstring)
BEGIN
    SET @nchar = SUBSTRING(@nstring, @position, 1)
    IF ASCII(@nchar) between 1 and 125
        SET @Result = @Result + @nchar
    SET @position = @position + 1
END

RETURN @Result

END

Eradicating 0 being the ASCII Null value!

Unicode (Hexadecimal) to varchar conversion

1 Answers1