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

Advertisements