SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[unqSplit](@String nvarchar(4000), @Delimiter char(1)) RETURNS @Results TABLE (value nvarchar(4000)) AS --this function takes two parameters; the first is the delimited string, the second is the delimiter BEGIN DECLARE @INDEX INT DECLARE @SLICE nvarchar(4000) -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z -- ERO FIRST TIME IN LOOP SELECT @INDEX = 1 IF @String IS NULL RETURN WHILE @INDEX !=0 BEGIN -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE IF @INDEX !=0 SELECT @SLICE = LEFT(@STRING,@INDEX - 1) ELSE SELECT @SLICE = @STRING -- PUT THE ITEM INTO THE RESULTS SET INSERT INTO @Results(value) VALUES(@SLICE) -- CHOP THE ITEM REMOVED OFF THE MAIN STRING SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) -- BREAK OUT IF WE ARE DONE IF LEN(@STRING) = 0 BREAK END RETURN END
How to use the functions.
Declare @tempString nvarchar(max) set @tempString = '''43C9D0A5-5FB7-45B9-BC2D-1D942B77A0CE'',''6c73f22f-7599-4e7a-bbdc-e71fe6e5f0f8'',''40d76edf-47d7-4e51-88fa-8bc3ae350d1c'',''3eece540-673e-42be-a123-17882e4e527f''' -- '''6c73f22f-7599-4e7a-bbdc-e71fe6e5f0f8'',''40d76edf-47d7-4e51-88fa-8bc3ae350d1c'',''3eece540-673e-42be-a123-17882e4e527f''' declare @tempTable table ( cid uniqueidentifier) insert into @tempTable select convert(uniqueidentifier, SUBSTRING(value,2,(LEN(value)))) from dbo.unqSplit(@tempString, ',') select * from @tempTable