Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:



share|improve this question
up vote 9 down vote accepted

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 )
share|improve this answer
Appreciate the answer alex. As I am unable to add tables to the db I have accepted Richards answer. Cheers – Jim Nov 6 '12 at 19:54
@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 Jan 17 '13 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! – Dan Oct 13 '15 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
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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