Click here to Skip to main content
Click here to Skip to main content

Parse Key Value Pairs from string in SQL

By , 7 Jul 2012
Rate this:
Please Sign up or sign in to vote.

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')

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Sumit Chawla
Software Developer (Senior)
United States United States
I am currently working as a Senior Software Developer. My primary skills include .NET, WPF,MSSQL,and C++. I have also worked in ASP.NET, XML, XSL, JavaScript,and Web Automation.
I love to solve problems,and love to do programming. In my idle time i love to explore new technologies and domains.

Comments and Discussions

 
Questionmore sql string parsers PinmemberMike Lang8-Jul-12 7:53 
AnswerRe: more sql string parsers PinmemberSumit Chawla8-Jul-12 8:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 8 Jul 2012
Article Copyright 2012 by Sumit Chawla
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid