Find the value of a name/value pair in a string

,

It has always bothered that there is not a NameValuePair function in SQL.

/*
*******************************************************************************
Created by:		Scott Williams
When created:	05/16/07
Purpose:		To find the value of a name/value pair in a string
*******************************************************************************

************************************************************************************************************
MODIFICATIONS
Date		Initials	Change Made
************************************************************************************************************

Example:	

SELECT dbo.NameValuePair('IssueID','Notification=802~|~ IssueID=10065~|~ ProjectID=300','~|~')

@MatchValue = The value you are looking for
@arrString = The array to search
@Delimiter = The delimiter

*/

CREATE FUNCTION [dbo].[NameValuePair]  (@MatchValue varchar(500),@arrString varchar(8000),@Delimiter varchar(15))
RETURNS varchar(8000)
AS


BEGIN
	DECLARE 
		@Value varchar(8000),
		@start int, 
		@end int

	--Finds where the value should start
	SET @start = CHARINDEX(@MatchValue,@arrString)+Len(@MatchValue)+1
	--Based on the delimiter, finds where the value ends
	SET @end = CHARINDEX(@Delimiter,@arrString,@start)
	--This makes sure if there is no delimiter after the last name/value pair it will still work
	IF @end=0 SET @end = len(@arrString)
	--Gets the value
	SET @Value = SUBSTRING(@arrString, @start, @end - @start)

  RETURN(@Value)
END

Rate

Share

Share

Rate