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 INTASBEGIN -- 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 ENDRETURN( @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 EmailChris Smith chris.smith@company.com