Convertir valores hexadecimales a uniqueidentifier

Es un problema poco frecuente, pero sí posible (mas adelante explicaré un caso real), que necesitemos convertir un valor hexadecimal a su representación uniqueidentifier.

Pongamos por caso, que disponemos del siguiente valor hexadecimal: 0xAF410B348743A84395FA5F37A3C32C8A. Si queremos obtener su representación uniqueidentifier, lo que tenemos que hacer es un simple cast de esta forma:

select convert(uniqueidentifier,0xAF410B348743A84395FA5F37A3C32C8A)

El valor uniqueidentifier que lo representa, dado al resolver la consulta, es el siguiente: 340B41AF-4387-43A8-95FA-5F37A3C32C8A

Como vemos, no hay problema alguno en realizar la conversión...salvo que el valor que estamos convirtiendo es un valor hexadecimal que puede que no tengamos almacenado en formato binario, sino como cadena de texto. Si el valor hexadecimal lo tenemos almacenado en una variable de tipo varchar, ya no es tan sencillo hacer la conversión:

Ejecutar la sentencia

select convert(uniqueidentifier,'0xAF410B348743A84395FA5F37A3C32C8A')

Nos lanzará este fantástico error:

Msg 8169, Level 16, State 2, Line 1 Conversion failed when converting from a character string to uniqueidentifier.

Podemos pensar (como es lógico), que lo suyo es convertir el valor primero a un tipo de datos binario (varbinary), y de este ya a su representación uniqueidentifier con el cast correpondiente.

Si lanzamos entonces la sentencia:

select convert(varbinary,'0xAF410B348743A84395FA5F37A3C32C8A')

Nos damos cuenta que el resultado no se parece en nada al valor que teníamos, y por tanto la conversión de este a uniqueidentifier no será válida:

select convert(uniqueidentifier,convert(varbinary,'0xAF410B348743A84395FA5F37A3C32C8A'))

Su resultado es 46417830-3134-4230-3334-383734334138, que no se parece ni de casualidad al valor correcto 340B41AF-4387-43A8-95FA-5F37A3C32C8A.

El proceso pues, requiere que transformemos "a manita" el valor hexadecimal almacenado en la cadena de texto, a su representación varbinary.

Para ello lo que debemos hacer es crearnos una función para tal fin, como puede ser esta:

 
ALTER FUNCTION [dbo].[HexStrToVarBin](@hexstr varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN
 DECLARE @hex char(2), @i int, @count int, @b varbinary(8000)
 SET @count = LEN(@hexstr)
 SET @b = CAST('' as varbinary(1))
 IF SUBSTRING(@hexstr, 1, 2) = '0x'
     SET @i = 3
 ELSE
     SET @i = 1
 WHILE (@i <= @count)
  BEGIN
     SET @hex = SUBSTRING(@hexstr, @i, 2)
     SET @b = @b +
         CAST(CASE WHEN SUBSTRING(@hex, 1, 1) LIKE '[0-9]'
             THEN CAST(SUBSTRING(@hex, 1, 1) as int)
             ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 1, 1)))-55 as int)
         END * 16 +
         CASE WHEN SUBSTRING(@hex, 2, 1) LIKE '[0-9]'
             THEN CAST(SUBSTRING(@hex, 2, 1) as int)
             ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 2, 1)))-55 as int)
         END as binary(1))
     SET @i = @i + 2
  END
 RETURN @b
END  

Una vez teniendo la función, ya podremos realizar la conversión tranquilamente:

select convert(uniqueidentifier,dbo.HexStrToVarBin '0xAF410B348743A84395FA5F37A3C32C8A'))

Por otro lado, el proceso contrario, es decir, convertir de varbinary a valor hexadecimal como cadena de texto lo podemos hacer con la función no documentada fn_varbintohexstr() que espera como argumento de entrada un valor hexadecimal (varbinary) y devuelve su misma representación, pero como cadena de texto.

SELECT master.dbo.fn_varbintohexstr(0xAF410B348743A84395FA5F37A3C32C8A)

El caso real del que os hablaba al principio es el de que querais realizar un join de la información proveniente de sysprocesses con tablas de información de jobs como por ejemplo sysjobs. En ese caso no tendreis mas remedio que realizar estas conversiones dado que en sysprocesses los identificadores de los jobs se encuentran en valor hexadecimal, mientras que los identificadores de las tablas de sysjobs (y sucesivas) se encuentran como uniqueidentifier.