Get Splitted Value From the String Format Specifie
Functuion Which returns splitted values from the string format specified in example
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Go
/***********************************************************************
Example : SELECT * FROM [dbo].[udf_utl_GetSplittedSearchCriterias]
( 'Name = Abhijit, Date Of Join = 27-April-1983, Job Profile = Database Developer', ',', '=' )
***********************************************************************/CREATE FUNCTION [dbo].[udf_utl_GetSplittedSearchCriterias]
(
@varInputString VARCHAR(4000),
@varCriteriaSeperator VARCHAR(1),
@varConditionSeperator VARCHAR(1)
)
RETURNS @tblSearchCriterias TABLE ( AttributeName VARCHAR(100), AttributeValue VARCHAR(1000) )
AS
BEGIN
WHILE @varInputString != ''
BEGIN
INSERT @tblSearchCriterias
SELECT RTRIM(LTRIM(SUBSTRING
( SUBSTRING( @varInputString, 1,
CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0
THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1
ELSE LEN( @varInputString )
END), 1,
CASE WHEN CHARINDEX
( @varConditionSeperator,
SUBSTRING
( @varInputString, 1,
CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0
THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1
ELSE LEN( @varInputString )
END
)
) = 0 THEN 0
ELSE CHARINDEX( @varConditionSeperator,
SUBSTRING
( @varInputString, 1,
CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0
THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1
ELSE LEN( @varInputString )
END
)
) - 1
END))),
RTRIM(LTRIM(SUBSTRING
( SUBSTRING( @varInputString, 1,
CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0
THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1
ELSE LEN( @varInputString )
END
),
CHARINDEX( @varConditionSeperator,
SUBSTRING( @varInputString, 1,
CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0
THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1
ELSE LEN( @varInputString )
END
)
)+ 1 , LEN(SUBSTRING( @varInputString, 1,
CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0
THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1
ELSE LEN( @varInputString )
END
)
)
)))
SET @varInputString = CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0
THEN SUBSTRING( @varInputString, CHARINDEX( @varCriteriaSeperator, @varInputString ) +1 , LEN(@varInputString ))
ELSE ''
END
END
RETURN
END
