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:



2 Answers 2


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))
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 )
  • 1
    @Jim: you don't need to add the tally table to that db. You can add it to master or maybe a DBA database and just reference it with full name.
    – Marian
    Commented Jan 17, 2013 at 17:12
  • Thanks! I used this technique to help me set and test for bit values in a large varbinary field. Breaking it down into discrete chunks allowed me to do the manipulation with low overhead! Commented Oct 13, 2015 at 23:11

A recursive approach to splitting the GUIDs.

CREATE FUNCTION dbo.splitGUIDs(@guids varbinary(max))
returns table as return
with CTE(item, remainder) as (
select CAST(CAST(left(@guids, 16) as binary(16)) as uniqueidentifier), stuff(@guids, 1, 16, '')
where @guids > ''
union all
select CAST(CAST(left(remainder, 16) as binary(16)) as uniqueidentifier), stuff(remainder, 1, 16, '')
from cte
where remainder > ''
select item from cte
--option (maxrecursion 0);

NOTE that I have put option(maxrecursion 0) into the function, but you need it in your outer query if your list of guids contains more than 100 entries, e.g.

  FROM dbo.splitGUIDS(@guids) x

Compared to using a Tally/Number table

  • good: doesn't depend on such a table
  • bad: could be slower
  • good: no inherent upper limit of items (Tally table size limits the number of items extracted using table method)
  • bad: you have to remember to use OPTION MAXRECURSION for large lists, in the caller query
  • The recursion limit could be significantly relaxed if the algorithmen uses a binary tree. Then you can decode 2^n values with a depth of n.
    – Marcel
    Commented Aug 25, 2023 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.