SQL Server 2005 - Convert any data to Binary String

[CVMichael]

The function takes binary data as input, but you can convert any data type to binary first, and then pass it to this function.

-- =============================================
-- Author:      <Michael Ciurescu>
-- Create date: <20090122>
-- Description: <Convert Binary to Binary String>
-- =============================================
CREATE FUNCTION dbo.f_BinToBinStr
(
    @Data VARBINARY(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @HexStr VARCHAR(MAX)
    
    /*
        -- Examples:
        SELECT dbo.f_BinToBinStr(RAND())
        SELECT dbo.f_BinToBinStr(NewID())
        SELECT dbo.f_BinToBinStr(CONVERT(VARBINARY(MAX), 'dfgdf34534dssds488dfk8'))
        SELECT dbo.f_BinToBinStr(CONVERT(VARBINARY(MAX), 123456789))
    */
    
    SET @HexStr = CONVERT(VARCHAR(MAX), master.dbo.fn_varbintohexstr(@Data))
    SET @HexStr = SUBSTRING(@HexStr, 3, LEN(@HexStr))
    
    SET @HexStr = REPLACE(@HexStr, '0', '0000')
    SET @HexStr = REPLACE(@HexStr, '1', '0001')
    SET @HexStr = REPLACE(@HexStr, '2', '0010')
    SET @HexStr = REPLACE(@HexStr, '3', '0011')
    SET @HexStr = REPLACE(@HexStr, '4', '0100')
    SET @HexStr = REPLACE(@HexStr, '5', '0101')
    SET @HexStr = REPLACE(@HexStr, '6', '0110')
    SET @HexStr = REPLACE(@HexStr, '7', '0111')
    SET @HexStr = REPLACE(@HexStr, '8', '1000')
    SET @HexStr = REPLACE(@HexStr, '9', '1001')
    SET @HexStr = REPLACE(@HexStr, 'A', '1010')
    SET @HexStr = REPLACE(@HexStr, 'B', '1011')
    SET @HexStr = REPLACE(@HexStr, 'C', '1100')
    SET @HexStr = REPLACE(@HexStr, 'D', '1101')
    SET @HexStr = REPLACE(@HexStr, 'E', '1110')
    SET @HexStr = REPLACE(@HexStr, 'F', '1111')
    
    RETURN @HexStr
END