Technical Article

Dynamic String Compare (W/O the dynamic sql)

,

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 instead of dynamic sql. YAY!  Allowing for the operator and value to be passed in as parms. So that the user on the front end can pick the comparison he wants to use against the field being searched.  I based my idea for this function upon 2 excellent articles from Robert Marda :
http://www.sqlservercentral.com/columnists/rmarda/howdynamiccanstaticbe.asp

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

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating