SQL Server finding string in Unicode HEX


  • I need to find part of string which is stored in a varbinary field on SQL Server 2008. The data that is stored in the field is in HEX Binary.

    For example the field has 0xA0000000001A000000000000000000000000000000000000000000000000000000850000002416002F002A002000480065006C006C006F0020002A002F0007

    I can ignore the first 37 characters. When I look at this in a SQL application it reads as follows after the first 37 characters

    I know that the hex binary is stored in Unicode format.

    My question is how can i search for the word 'Hello' using a SQL Statement. I tried the following below but i do not get any results

    SELECT CONVERT(varchar(max),fieldname) from tablename where fieldname like '%Hello%;

    I would really appreciate any help that I can get



    Friday, September 20, 2013 11:14 PM