IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CF_JSON_VALUE') 
                   and type = 'FN')
BEGIN
    DROP FUNCTION dbo.CF_JSON_VALUE
END
GO


CREATE FUNCTION [dbo].[CF_JSON_VALUE](@pJson VARCHAR(8000), @pVar VARCHAR(4000))
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Retorno VARCHAR(4000)
DECLARE @PosVar  SMALLINT
DECLARE @PosDatoInicio  SMALLINT
DECLARE @PosDatoFin  SMALLINT


SET @PosVar =  CHARINDEX('"' + UPPER(@pVar) + '"',UPPER(@pJson),1) 
IF @PosVar <> 0
BEGIN
     IF SUBSTRING(@pJson,@PosVar + LEN(@pVar) + 3,1) = '['
	 BEGIN
	    SET @PosDatoInicio = @PosVar + LEN(@pVar) + 4
	    SET @PosDatoFin = CHARINDEX(']',UPPER(@pJson),@PosDatoInicio) - 1
		SET @Retorno    = SUBSTRING(@pJson,@PosDatoInicio,@PosDatoFin - @PosDatoInicio + 1)
	 END

     IF SUBSTRING(@pJson,@PosVar + LEN(@pVar) + 3,1) = '{'
	 BEGIN
	    SET @PosDatoInicio = @PosVar + LEN(@pVar) + 4
	    SET @PosDatoFin = CHARINDEX('}',UPPER(@pJson),@PosDatoInicio) - 1
		SET @Retorno    = SUBSTRING(@pJson,@PosDatoInicio,@PosDatoFin - @PosDatoInicio + 1)
	 END


     IF SUBSTRING(@pJson,@PosVar + LEN(@pVar) + 3,1) = '"'
	 BEGIN
	    SET @PosDatoInicio = @PosVar + LEN(@pVar) + 4
	    SET @PosDatoFin = CHARINDEX('"',UPPER(@pJson),@PosDatoInicio) - 1
		SET @Retorno    = SUBSTRING(@pJson,@PosDatoInicio,@PosDatoFin - @PosDatoInicio + 1)
	 END
	 
	 IF SUBSTRING(@pJson,@PosVar + LEN(@pVar) + 3,1) NOT IN ('"','{','[')
	 BEGIN
	    SET @PosDatoInicio = @PosVar + LEN(@pVar) + 3
	    SET @PosDatoFin = CHARINDEX(',',UPPER(@pJson),@PosDatoInicio) - 1
		IF  @PosDatoFin = -1 
			SET @PosDatoFin = CHARINDEX('}',UPPER(@pJson),@PosDatoInicio) - 1
	    SET @Retorno    = SUBSTRING(@pJson,@PosDatoInicio,@PosDatoFin - @PosDatoInicio + 1)
	 END
END

RETURN @Retorno

END
