T-sql – T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs

functionst-sql

I am trying to write a function which will split a varbinary(max) field into a table with 16 byte GUIDs, but am struggling. I haven't been able to find any examples or walkthroughs on how to do this using varbinaries – so any help appreciated. An example of the data in the varbinary field I am trying to split is:

0x09B57E7B1E19CD448698ECDA170B080F7C98FD411E5D264EB6D83AD6D4067C0D336E73299D2408438B625B09BED408D2D8E6C6DE4310194ABD90CF90F1558838731B2B740B508E44843FA98125ADF599669FC433BD4C074294C64E13D880A3ED78D25157C1E7A946B5B9C78E8DA0B5A8ECA73BB5CE59E6439586C445F25F2CDCA2B69483A7AE184A922DBC895C2EEB47910B88E915B7B94FB0EFC84568E2C83F0EF3168F62B6CB4EBA849486164F6DA7805C85383FBF694DB606904E0BA1364DC2DECDC1D00DED4BA28D0F1AA3DD9D1E1AA98697B914924995A26512C7EBD7C1

Thanks

Best Answer

This is easy if you have a numbers table. The following example is cut and pasted from Erland Sommarskog's site:

CREATE FUNCTION fixbinary_single(@str varbinary(MAX))
RETURNS TABLE AS
RETURN(SELECT listpos = n.Number,
              n = convert(int, substring(@str, 4 * (n.Number - 1) + 1, 4))
       FROM   Numbers n
       WHERE  n.Number <= datalength(@str) / 4 )
Related Question