Dynamic String Compare (W/O the dynamic sql)

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnStringCompare]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnStringCompare]
GO

CREATE      FUNCTION fnStringCompare(@String1 nvarchar(4000),
@Operator varchar(40) = '=',
@String2 nvarchar(4000))
RETURNS smallint
AS
BEGIN
-- '---------------------------------------------
-- ' Function fnStringCompare
-- '
-- ' Created by tcartwright / The System Shop Inc.
-- ' Date: 12-06-2002    Time: 23:14
-- ' 
-- ' Purpose: To compare two strings with a supplied operator and return true if the strings 
-- ' evaluate to true using the operator provided. This is useful for stored procs to 
-- ' use for where clauses instead of dynamic sql. YEE-HA! Allowing for the operator and value to be 
-- ' passed in as parms. I based my idea for this function upon 2 excellent 
-- ' articles from Robert Marda :
-- '1) http://www.sqlservercentral.com/columnists/rmarda/howdynamiccanstaticbe.asp
-- '2) http://www.sqlservercentral.com/columnists/rmarda/howdynamicsqlcanbestatic2.asp
-- '
-- ' EX: SELECT dbo.fnStringCompare('test', default, 'test')
-- '
-- 'OR
-- '
-- 'SELECT * FROM TABLE WHERE dbo.fnStringCompare(TABLE.COLUMN, 'LIKE %VAL%', 'test') = 1
-- '
-- 'OR
-- '
-- '     CREATE PROCEDURE spWhereTest(@au_fname varchar(40) = NULL, @au_fname_op varchar(30))                   
-- '     AS                                                                                           
-- '     SELECT * FROM pubs..authors WHERE dbo.fnStringCompare(au_fname, @au_fname_op, @au_fname) = 1
-- '     GO                                                                                           
-- '                                                                                                  
-- '     -- =============================================                                             
-- '     -- example(s) to execute the store procedure                                                    
-- '     -- =============================================                                             
-- '     EXECUTE spWhereTest 'e', 'LIKE %VAL%' --contains                                             
-- '     GO                                                                                           
-- '                                                                                                  
-- '     EXECUTE spWhereTest 'e', 'LIKE %VAL' --ends with                                             
-- '     GO                                                                                           
-- '
-- '     EXECUTE spWhereTest default, '=' --null string, and not checing for nullable so all records                                             
-- '     GO                                                                                           
-- '
-- '     EXECUTE spWhereTest default, 'IS NULL' --IS NULL
-- '     GO                                                                                           
-- '
-- 'DROP PROCEDURE spWhereTest
-- 'GO
-- '
-- ' NOTE : Depending upon the collation of your sql server 
-- 'text case may be important and affect the results
-- '
-- ' Returns (smallint) :
-- '-1 : Null or empty string operator
-- '-2 : Invalid operator type
-- ' 1 : The expression was Valid
-- ' 0 : None of the above was true, and somehow got 
-- 'past my checks, should never happen
-- '---------------------------------------------

--check if they sent in an operator
IF @Operator IS NULL OR LEN(RTRIM(LTRIM(@Operator))) = 0
RETURN -1

--prep the operator
SET @Operator = UPPER(LTRIM(RTRIM(@Operator)))
--remove any arbitrary single quotes
SET @Operator = REPLACE(@Operator, CHAR(39), '')

--check for a Valid operator
IF PATINDEX('%;' + @Operator + ';%', ';=;!=;<;<=;>;>=;LIKE %VAL%;NOT LIKE %VAL%;LIKE VAL%;NOT LIKE VAL%;LIKE %VAL;NOT LIKE %VAL;IS NULL;IS NOT NULL;') <= 0
RETURN -2

--sent in a null @string2 but not checking for nullable 
--so it invalidates the rest of the checks
--allows for the possibility to NOT check
IF @String2 IS NULL AND (@Operator <> 'IS NULL' AND @Operator <> 'IS NOT NULL')
RETURN 1

IF @Operator = '=' AND @String1 = @String2
RETURN 1

IF (@Operator = '!=' OR @Operator = '<>') AND @String1 <> @String2
RETURN 1

IF @Operator = '<' AND @String1 < @String2
RETURN 1

IF @Operator = '<=' AND @String1 <= @String2
RETURN 1


IF @Operator = '>' AND @String1 > @String2
RETURN 1

IF @Operator = '>=' AND @String1 >= @String2
RETURN 1

--Contains
IF @Operator = 'LIKE %VAL%' AND @String1 LIKE '%' + @String2 + '%'
RETURN 1

--Does not contain
IF @Operator = 'NOT LIKE %VAL%' AND @String1 NOT LIKE '%' + @String2 + '%'
RETURN 1

--begins with
IF @Operator = 'LIKE VAL%' AND @String1 LIKE @String2 + '%'
RETURN 1

--does not begin with
IF @Operator = 'NOT LIKE VAL%' AND @String1 NOT LIKE @String2 + '%'
RETURN 1

--starts with
IF @Operator = 'LIKE %VAL' AND @String1 LIKE '%' + @String2
RETURN 1

--does not start with
IF @Operator = 'NOT LIKE %VAL' AND @String1 NOT LIKE '%' + @String2
RETURN 1

--is null :)
IF @Operator = 'IS NULL' AND @String1 IS NULL
RETURN 1

--is not null
IF @Operator = 'IS NOT NULL' AND @String1 IS NOT NULL
RETURN 1

--IF we got here something is not right :( or we have a PEBCAK issue 
--of course it can't be my code..... :D LOL {{Tim C 12-06-2002 23:13:25}}
--OR it COULD be that the check was invalid.........
RETURN 0
END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO