declare @s varchar(max)
declare @1 varchar(max)

set @s= ([Select Statement goes here])
set @1 = (select substring(@s,charindex('[1st delimiter]',@s)+len('[1st delimiter]'),charindex('[2nd delimiter]',@s)-charindex('[1st delimiter]',@s)-len('[1st delimiter]')))

--**************************************************************
--* Name:       fn_MyFindPosition @TargetStr, @SearchedStr, @Occurrence
--* Description: Find the Nth Occurrence of a Character in a String
--* Parameter Info:
--*		@TargetStr - String value to search within
--*		@SearchedStr - Value of Nth Occurrence of the Character/String
--*		@Occurrence - Number (N) of Nth Occurrence
--**************************************************************
ALTER FUNCTION [dbo].[fn_MyFindPosition]
(
@TargetStr VARCHAR(8000), 
@SearchedStr VARCHAR(8000), 
@Occurrence INT
)
RETURNS INT
AS
BEGIN
  -- Declare local variables
  DECLARE @Position INT, @Counter INT, @Return INT

  SET @Position = CHARINDEX(@TargetStr, @SearchedStr)
  SET @Counter = 1

  IF @Occurrence = 1 
    BEGIN
      SET @Return = @Position
    END
  ELSE
    BEGIN
      WHILE (@Counter < @Occurrence)
        BEGIN
          SELECT @Return = CHARINDEX( @TargetStr, @SearchedStr, @Position + 1 )
          SET @Counter = @Counter + 1
          SET @Position = @Return
        END
    END

RETURN( @Return )
	
END

DECLARE 
  @MultiValueCSVField VARCHAR(200) = 'userid01,4,Chris,Smith,chris.smith@company.com,True,False|', 
  @DelimVal VARCHAR(1) = ','

SELECT	
[UserName] = 
   REPLACE( SUBSTRING( @MultiValueCSVField, dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 2 ) + 1 , 
      ( dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 4 ) - dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 2 ) ) - 1 ), @DelimVal, ' ' ),
[UserEmail] = 
     SUBSTRING( @MultiValueCSVField, dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 4 ) + 1 , 
               ( dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 5 ) - dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 4 ) ) - 1 )

UserName        SurveyTaker Email
Chris Smith     chris.smith@company.com