ALTER FUNCTION[dbo].[EnumValidate1]
(
@P_Input NVARCHAR(55)
)
RETURNS
@returnList TABLE ([@V_RESULT] [nvarchar] (500))
AS
BEGIN
DECLARE @V_EnumValue NVARCHAR(255)
DECLARE @V_pos INT
DECLARE @V_RESULT NVARCHAR(255)
SET @V_EnumValue = 'A,D,T,Y,Z';
WHILE CHARINDEX(',', @V_EnumValue) <0 AND CHARINDEX(',', @V_EnumValue)< LEN(@V_EnumValue)
BEGIN
SELECT @V_pos = CHARINDEX(',', @V_EnumValue)
SET @V_EnumValue = SUBSTRING(@V_EnumValue, 1, @V_pos-1)
END
INSERT INTO @returnList
SELECT @V_RESULT
IF @P_Input != @V_EnumValue
BEGIN
SET @V_RESULT ='NOT MATCHED'
INSERT INTO @returnList
SELECT @V_RESULT
RETURN
END
SET @V_RESULT= 'MATCHED'
INSERT INTO @returnList
SELECT @V_RESULT
RETURN
END
example: select[dbo].EnumValidate1
It shall return matched
example2:select[dbo].EnumValidate1
it shall return unmatched