Parse Key Value Pairs from string in SQL

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s