Convert SQL result from Hash to Varchar

I have some data in an SQL table that is hashed, and am trying to convert these variables back to a string, however everything I get back is gibberish.

I have tried all suggestions on Stack Overflow, including removing all the sequential double zeros ’00’.

I don’t know what the unhash-ed variables look like, but I assume it will be a string variable. How would we even know where to start determining what sort of hash was applied to this field in the first place?

Any help or guidance is very appreciated.

My attempts so far have been:

SELECT CASE WHEN LEN(0x00007041673F000000007041B2060000) > 0 THEN
    convert(varchar(max),     
        convert(varbinary(max),
            REPLACE(
            convert(varchar(max),0x00007041673F000000007041B2060000, 1)
            ,'00',''
            )
        ,1)
    ,1)
    ELSE '' END
    
  ,CONVERT(varbinary(64),0x00007041673F000000007041B2060000, 1)
  ,CONVERT(varchar(64), 0x00007041673F000000007041B2060000, 1)
  ,CONVERT(varchar(64), 0x00007041673F000000007041B2060000, 2)


--Other Hash Variables-
--0x00007041673F000000007041B2060000
--0x0000C84271EB0000
--0x0000C842683F0000
--0x0000C842693F0000
--0x0000C842703F00000000C842775A0200
--0x0000A041873F00000000A041F9050000
--0x0000C842264000000000C842F04F0100
--0x000034427C400000000034426E4A0000

EDIT – Have also attempted BASE64 DECODE on SQL- https://dba.stackexchange.com/questions/191273/decode-base64-string-natively-in-sql-server

SELECT 
    CONVERT
    (
        VARCHAR(MAX), 
        CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
    ) AS RESULT
FROM
    (
        SELECT '0x00007041673F000000007041B2060000' AS BASE64_COLUMN
    ) A

Answer

If this is really a hash value, there is no realistic chance to revert the value to its value.

But, looking at your values, I doubt, that these are really hash values. It is very unusual to get such close binary representations. Try to use any hash function against any value. You would not get results such similar…

Without any knowledge about your needs, you might try to take the binary value as any (16 byte?) type or chain of smaller types. Here I use BIGINT (two 8-byte-values), you can try UNIQUEIDENTIFIER, or you might take this as a chain of 16 1-byte values, a chain of 8 2-byte values or any other viable interpretation.

DECLARE @tbl TABLE(YourBinary VARBINARY(MAX));
INSERT INTO @tbl VALUES
--Other Hash Variables-
 (0x00007041673F000000007041B2060000)
,(0x0000C84271EB0000                )
,(0x0000C842683F0000                )
,(0x0000C842693F0000                )
,(0x0000C842703F00000000C842775A0200)
,(0x0000A041873F00000000A041F9050000)
,(0x0000C842264000000000C842F04F0100)
,(0x000034427C400000000034426E4A0000);

SELECT DATALENGTH(YourBinary) AS TheByteLength
      ,CAST(YourBinary AS UNIQUEIDENTIFIER) AS CastedToGuid
      ,A.*
      ,CAST(A.Left8 AS BIGINT) AS Left8_Bigint
      ,CAST(A.Right8 AS BIGINT) AS Right8_Bigint
FROM @tbl
CROSS APPLY(SELECT CONVERT(BINARY(8),LEFT(YourBinary,8),0) AS Left8
                  ,CASE WHEN DATALENGTH(YourBinary)=16 THEN CONVERT(BINARY(8),RIGHT(YourBinary,8),0) END AS Right8) A;

One guess: This might be a combined key of two numbers. Might be a tenant and a table key in a binary combination. It is obvious, that the values consist of very similar numbers. Those with 16 bytes include rather near values.

Left8_Bigint    Right8_Bigint
123426207367168 123427461922816
220187704623104 NULL
220187542355968 NULL
220187559133184 NULL
220187676573696 220187795784192
176203302371328 176205211172864
220186435125248 220189825106176
57460157054976  57459922829312

It might help to tell us more about your problem’s background…

UPDATE

Taking this as chain of 4-byte-int-values returns rather interesting results:

SELECT CAST(Byte4_1 AS INT)
      ,CAST(Byte4_2 AS INT)
      ,CAST(Byte4_3 AS INT)
      ,CAST(Byte4_4 AS INT)
FROM @tbl
CROSS APPLY(SELECT CONVERT(BINARY(4),LEFT(YourBinary,4),0) AS Byte4_1
                  ,CONVERT(BINARY(4),SUBSTRING(YourBinary,5,4),0) AS Byte4_2
                  ,CONVERT(BINARY(4),SUBSTRING(YourBinary,9,4),0) AS Byte4_3
                  ,CONVERT(BINARY(4),SUBSTRING(YourBinary,13,4),0) AS Byte4_4) A;

The result

int1    int2        int3    int4
28737   1732182016  28737   -1308229632
51266   1911226368  0       0
51266   1748959232  0       0
51266   1765736448  0       0
51266   1883176960  51266   2002387456
41025   -2025914368 41025   -117112832
51266   641728512   51266   -263257856
13378   2084569088  13378   1850343424

int1 and int3 seems to be the same value. This points to any kind of combined key…