Parse Key Value Pairs from string in SQL

Introduction

Here is a nice little SQL function that could help you extract key value pairs from a string. This can be really helpful in parsing log statements/audit logs stored in SQL servers.

CREATE FUNCTION dbo.KeyValuePairs( @inputStr VARCHAR(MAX)) 
RETURNS @OutTable TABLE 
	(KeyName VARCHAR(MAX), KeyValue VARCHAR(MAX))
AS
BEGIN

	DECLARE @separator CHAR(1), @keyValueSeperator CHAR(1)
	SET @separator = ','
	SET @keyValueSeperator = ':'

	DECLARE @separator_position INT , @keyValueSeperatorPosition INT
	DECLARE @match VARCHAR(MAX) 
	
	SET @inputStr = @inputStr + @separator
	
	WHILE PATINDEX('%' + @separator + '%' , @inputStr) <> 0 
	 BEGIN
	  SELECT @separator_position =  PATINDEX('%' + @separator + '%' , @inputStr)
	  SELECT @match = LEFT(@inputStr, @separator_position - 1)
	  IF @match <> '' 
		  BEGIN
            SELECT @keyValueSeperatorPosition = PATINDEX('%' + @keyValueSeperator + '%' , @match)
            IF @keyValueSeperatorPosition <> -1 
              BEGIN
        		INSERT @OutTable
				 VALUES (LEFT(@match,@keyValueSeperatorPosition -1),_
				 RIGHT(@match,LEN(@match) - @keyValueSeperatorPosition))
              END
		   END		
 	  SELECT @inputStr = STUFF(@inputStr, 1, @separator_position, '')
	END

	RETURN
END
GO

Usage

 SELECT * FROM dbo.KeyValuePairs('FirstName:First,LastName:last')