Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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);
GO

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.

SELECT item
  FROM dbo.splitGUIDS(@guids) x
OPTION (MAXRECURSION 0);

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

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 )
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

Your Answer

 
discard

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.