e.g string = "Param1=3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters.

Currently am using the below function and getting the parameters by each in select statement as mentioned below.

select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1, [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2 CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex] ( @CustomProp varchar(max), @StringName Varchar(50), @Separator char(1) ) RETURNS varchar(max) AS BEGIN IF(LEN(@CustomProp)> 0 AND RIGHT(@CustomProp, 1) <> ';') BEGIN SET @CustomProp = @CustomProp+';' END DECLARE @vcduration varchar(max) if(@Separator is null) begin Set @vcduration = case when CHARINDEX(@StringName,@CustomProp,1)!=0 then SUBSTRING(@CustomProp,CHARINDEX(@StringName,@CustomProp,1)+len(@StringName),((CHARINDEX(@StringName,@CustomProp,1)+len(@StringName)))) else null end end else begin Set @vcduration = case when CHARINDEX(@StringName,@CustomProp,1)!=0 then SUBSTRING(@CustomProp,CHARINDEX(@StringName,@CustomProp,1)+len(@StringName),(CHARINDEX(@Separator,@CustomProp,CHARINDEX(@StringName,@CustomProp,1))-(CHARINDEX(@StringName,@CustomProp,1)+len(@StringName)))) else null end end RETURN @vcduration END

I need any alternate solution for the above. For the Output as below,

-----------------------------------

Param1 param2 param4 param6

----------------------------------

3 4 testval 11

--------------------------------