IEEE754

I wrote this project because I wanted to see the value that was stored using REAL or FLOAT. It is not always what you think it is.

The IEEE754 function automatically detects which precision is needed; Half precision (16-bit), single precision (32-bit), double precision (64 bit), quadruple precision (128 bit), or octuple precision (256 bit). All other precision will return NULL.

Single precision is used for REAL and double precision is used for FLOAT, in SQL Server.

You can take a look here to see how IEEE754 is decoded.

DROP FUNCTION dbo.IEEE754;
GO
DROP ASSEMBLY [sqlTopia];
GO
CREATE ASSEMBLY [sqlTopia]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300DE42805F0000000000000000E00022200B013000001200000006000000000000C6310000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000743100004F00000000400000A802000000000000000000000000000000000000006000000C0000003C3000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000CC110000002000000012000000020000000000000000000000000000200000602E72737263000000A8020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200000000000000000000000000000000A8310000000000004800000002000500BC290000800600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133005004C010000010000110072010000700A026F0500000A17590B036F0500000A17590C0716FE04130411042C0B0072030000701000160B000816FE04130511052C0B0072030000701001160C00160D38AD00000000071632090816FE0416FE012B0116130611062C2200090207176F0600000A280700000A0308176F0600000A280700000A58580D002B330716FE0416FE01130711072C1400090207176F0600000A280700000A580D002B1200090308176F0600000A280700000A580D00091F0AFE0416FE01130811082C1A00091F0A5913091209280800000A06280900000A0A170D002B12001203280800000A06280900000A0A160D000717590B0817590C0007162F090816FE0416FE012B0117130A110A3A3CFFFFFF0917FE0416FE01130B110B2C10001203280800000A06280900000A0A00067201000070280A00000A130C110C2C080072030000700A0006130D2B00110D2A13300500160100000200001100026F0500000A0A036F0500000A0B72010000700C72010000700D0616FE01130711072C0B0072030000701000170A000716FE01130811082C0B0072030000701001170B00061759130938AE00000000021109176F0600000A280700000A130516130672010000701304071759130A2B3C001106110503110A176F0600000A280700000A5A58130611061F0A5D130B120B280800000A1104280900000A130411061F0A5B130600110A1759130A110A16FE0416FE01130C110C2DB6110617FE0416FE01130D110D2C12001206280800000A1104280900000A13040009110408280900000A28010000060D087203000070280900000A0C00110917591309110916FE0416FE01130E110E3A41FFFFFF09130F2B00110F2A0000133002002D000000030000110072070000700A2B130006720B00007028020000060A0217591000000217FE0416FE010B072DE2060C2B00082A0000001330030058000000040000110072030000700A160B026F0500000A17590C2B2F000208176F0600000A7207000070280A00000A0D092C0F000607280300000628010000060A000717580B000817590C0816FE0416FE01130411042DC40613052B0011052A1330050064010000050000110072010000700A026F0500000A0B036F0500000A0C0716FE01130511052C0B0072030000701000170B000816FE01130611062C0B0072030000701001170C00026F0500000A036F0500000A280B00000A0D161304091759130738B6000000001107072F07110708FE042B0116130811082C26001104021107176F0600000A280700000A031107176F0600000A280700000A58581304002B37110707FE04130911092C17001104021107176F0600000A280700000A581304002B15001104031107176F0600000A280700000A5813040011041F0AFE0416FE01130A110A2C1C0011041F0A59130B120B280800000A06280900000A0A171304002B13001204280800000A06280900000A0A1613040000110717591307110716FE0416FE01130C110C3A39FFFFFF110417FE0416FE01130D110D2C10001204280800000A06280900000A0A00067201000070280A00000A130E110E2C080072030000700A0006130F2B00110F2A133004008E0000000600001100720F0000700A2B700072010000700B160C1613042B34001F0A085A061104176F0600000A280700000A580C08185B0D071203280800000A280900000A0B0818095A590C001104175813041104066F0500000AFE04130511052DBC081F0A5A0C0708185B13061206280800000A280900000A0A0217591000000217FE0416FE01130711072D830613082B0011082A0000133003004B000000070000110072030000700A160B2B2B000207176F0600000A7207000070280A00000A0C082C0F000607280600000628050000060A00000717580B07026F0500000AFE040D092DC80613042B0011042A0013300500D7030000080000110072010000700A1613092B2100060211099118280C00000A1E1F306F0D00000A280900000A0A001109175813091109028E69FE04130A110A2DD2720100007013057201000070130672010000701307028E69130C110C130B110B1A3011110B182E2F2B00110B1A2E6C3884010000110B1E3BA40000002B00110B1F103BE20000002B00110B1F203B1D01000038610100000616176F0600000A0B06171B6F0600000A0D0928040000060D09280700000A1F0F590C1202280800000A0D061C1F0A6F0600000A130411042807000006130438290100000616176F0600000A0B06171E6F0600000A0D0928040000060D09280700000A1F7F590C1202280800000A0D061F091F176F0600000A130411042807000006130438E40000000616176F0600000A0B06171F0B6F0600000A0D0928040000060D09280700000A20FF030000590C1202280800000A0D061F0C1F346F0600000A1304110428070000061304389B0000000616176F0600000A0B06171F0F6F0600000A0D0928040000060D09280700000A20FF3F0000590C1202280800000A0D061F101F706F0600000A13041104280700000613042B550616176F0600000A0B06171F136F0600000A0D0928040000060D09280700000A20FFFF0300590C1202280800000A0D061F1420EC0000006F0600000A13041104280700000613042B0C7E0E00000A130D38D6010000077207000070280A00000A130E110E2C0A0072130000700B002B080072010000700B00081FF32E1F081F812E1A082001FCFFFF2E12082001C0FFFF2E0A08200100FCFFFE012B0117130F110F2C1800077203000070280900000A280F00000A130D38700100000816FE0413101110398B0000000008281000000A175928060000060D096F0500000A11046F0500000A581308091104280200000611081F306F0D00000A13050911052805000006130511051611056F0500000A1108596F0600000A178D0C00000125161F309D6F1100000A1306110511056F0500000A11085911086F0600000A178D0C00000125161F309D6F1200000A13070038880000000816FE02131111112C71000828030000060D11046F0500000A13080911042802000006130511051611056F0500000A1108596F0600000A13061106092801000006178D0C00000125161F309D6F1100000A1306110511056F0500000A11085911086F0600000A178D0C00000125161F309D6F1200000A1307002B0D0072070000701306110413070011067201000070280A00000A131211122C0900720300007013060011077201000070281300000A131311132C100072170000701107280900000A1307000711061107281400000A280F00000A130D2B00110D2A2202281500000A002A42534A4201000100000000000C00000076342E302E33303331390000000005006C00000054020000237E0000C00200007402000023537472696E677300000000340500001C0000002355530050050000100000002347554944000000600500002001000023426C6F620000000000000002000001471502000900000000FA013300160000010000000C00000002000000090000000B00000015000000040000000800000001000000020000000000280101000000000006008E00A1010600AE00A101060065008E010F00C1010000060016023A010A00790069010A00D200D001060007013A01060044023A01060011003A01060018013A01060064013A01000000002D000000000001000100010010005C0000001500010001005020000000009100F80134000100A821000000009100500234000300CC220000000091004D01D80005000823000000009100FA00DD0006006C23000000009100E50134000700DC240000000091005801D80009007825000000009100DC00DD000A00D0250000000096002500E2000B00B329000000008618840106000C00000001000100000002001700000001000700000002001D00000001003102000001000A02000001000100000002001700000001004101000001000A0200000100CC00090084010100110084010600190084010A0031008401060041001D01210041000E01250049000F002B005100F100300041000F023400410059023A0059004C0275004900F100A70041001D02AD0039003501B30039002502B70059008A01BD0041003A02C20041005400C200410065023A0041000F02C8002900840106002E000B00E9002E001300F2002E001B001101000123001A01100040005300590062007B0087008F0004800000000000000000000000000000000036000000040000000000000000000000CF004B0000000000040000000000000000000000CF003F00000000000000007465726D3100666163746F723100546F496E743332007465726D3200666163746F72320049454545373534003C4D6F64756C653E0073716C546F7069610053797374656D2E44617461006D73636F726C6962005472696D456E640044617461626173650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650076616C75650053716C537472696E670042697473546F4672616374696F6E537472696E6700546F537472696E670042697473546F496E7465676572537472696E6700537562737472696E67004D617468006765745F4C656E6774680073716C546F7069612E646C6C004E756C6C0053797374656D00626974706F736974696F6E00506F7765724F6654776F00446976696465427954776F0043686172004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72004162730053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C5479706573004164644672616374696F6E537472696E677300416464496E7465676572537472696E6773006269747300436F6E636174004F626A656374005061644C656674006F705F496D706C69636974006578706F6E656E74005472696D537461727400436F6E76657274004D6178004D756C7469706C79006F705F457175616C697479006F705F496E657175616C697479000000010003300000033100000332000003350000032D0001032E000000A038D18EBD55EF4F83B88F7A077A7F780004200101080320000105200101111110070E0E0808080202020202080202020E032000080520020E0808040001080E0320000E0500020E0E0E050002020E0E12071008080E0E0E080802020808080202020E0507030E020E0807060E080802020E1207100E08080808020208020202080202020E0500020808080B07090E0E0808080208020E0707050E0802020E1707140E0E080E0E0E0E0E0808020808111D0202020202020500020E05080520020E08030306111D050001111D0E04000108080520010E1D030600030E0E0E0E08B77A5C561934E0890400010E080400010E0E060001111D1D050801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730108010007010000000004010000000000000000DE42805F00000000020000001C010000583000005812000052534453CA7C7A80D56454419B18845CEE46B70301000000433A5C55736572735C6C617273737065745C736F757263655C7265706F735C73716C546F7069615C73716C546F7069615C6F626A5C44656275675C73716C546F7069612E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009C3100000000000000000000B6310000002000000000000000000000000000000000000000000000A8310000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003A000D00010049006E007400650072006E0061006C004E0061006D0065000000730071006C0054006F007000690061002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000042000D0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000730071006C0054006F007000690061002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000C83100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.IEEE754
(
     @value VARBINARY(32)
)
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER,
     RETURNS NULL ON NULL INPUT
AS   EXTERNAL NAME sqlTopia.[Database].IEEE754;
GO

-- Test case
DECLARE @Data TABLE
(
     value1 REAL NULL,
     value2 FLOAT NULL
);

INSERT @Data
SELECT a.n,
       b.n
FROM   (
            VALUES (NULL),
                   (2.74),
                   (-2.74),
                   (0.74),
                   (-0.74),
                   (1.74),
                   (-1.74),
                   (0)
           ) AS a(n)
CROSS JOIN (
            VALUES (NULL),
                   (2.74),
                   (-2.74),
                   (0.74),
                   (-0.74),
                   (1.74),
                   (-1.74),
                   (0)
           ) AS b(n);
SELECT value1,
       value2,
       dbo.IEEE754(value1),
       dbo.IEEE754(value2)
FROM   @Data;