Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / T-SQL
Tip/Trick

Parse Key Value Pairs from string in SQL

Rate me:
Please Sign up or sign in to vote.
4.33/5 (3 votes)
7 Jul 2012CPOL 33.6K   3   2
Describes how to parse and extract key value pairs from a string or column 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.

SQL
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

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


Written By
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 Pin
Mike Lang8-Jul-12 7:53
Mike Lang8-Jul-12 7:53 
AnswerRe: more sql string parsers Pin
Sumit Chawla8-Jul-12 8:02
Sumit Chawla8-Jul-12 8:02 

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

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