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
DateInitialsChange 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