Create unhex() function for MSSQL

Our company stores data from Avaya into MySQL database. While our applications are using MSSQL for storage.

We have our two databases connected having MySQL as a Linked Server in MSSQL.

From MSSQL, we tried:

    SELECT *
    FROM OPENQUERY([MySQL_DB], 
        'SELECT unhex(column1)
        FROM table1')

The MySQL query works if I run it in MSSQL Query Browser but it doesn't work in Stored Procedures. This is why I have created this user-defined function in MSSQL to replicate the unhex() function of MySQL.

Here is the code:

    CREATE FUNCTION [dbo].[unhex] (@input_text varchar(255))
    RETURNS varchar(255)
    AS
    BEGIN
    declare @unhex varchar(255)
    declare @position int, @length int
    declare @pair varchar(2),@equivalent varchar(1)

    set @position = -1
    set @length = len(@input_text)
    set @unhex = ''

    WHILE(@position+2 < @length)
    BEGIN
    set @position = @position + 2
    set @pair = SUBSTRING(@input_text, @position, 2)

    SELECT @equivalent = char(cast('' as xml).value('xs:hexBinary(
        substring(sql:variable("@pair"), 
        sql:column("t.pos")) )', 'varbinary(max)'))
    FROM (select case substring(@pair, 1, 2) 
    when '0x' then 3 
    else 0 
    end) 
    AS t(pos)
    set @unhex = @[email protected]
    END

    RETURN @unhex

    END

Once the user-defined function is created, I then modified my query like this:

    SELECT *
    FROM OPENQUERY([MySQL_DB], 
    'SELECT dbo.unhex(column1)
    FROM table1')