Technical Article

Case Sensitive String Compare User Function

,

The script provided installs a Sql Server User Defined function which will perform a case sensitive string compare similar to the compare script submitted by Mike McManus.  The script also includes test code which may be highlighted to test the function once it is installed and provides examples of usage.

-- CaseSensitiveCompare.udf
-- 
-- Author: Mark Howard
-- Date: 2/17/03

-- After reviewing the CaseCompare script submitted to SqlServerCentral.com by 
-- Mike McManus it seemed to me that a user defined function would be more useful.
-- This function is different in its approach to performing a case sensitive
-- compare, to the structure of the program, and in the values returned by the
-- function.  It is offered as an alternative method to Mikes's.
--
-- Returns: 
--    -1 if one or both of the string are null.
--     1 if the strings are equal
--     0 if the strings are not equal. 
-- Please feel free to copy, use, and change in any way.

create function CaseSensitiveCompare(@String1 varchar(1024), @String2 varchar(1024)) returns int
as
begin
    declare
        @CharPosition   int,
        @ReturnValue    int,
        @StringsLength  int
        
    -- Check that the string are not null.
    if @String1 is not null and @String2 is not null
    begin
        -- If the strings are equal in length then we can proceed to check each character.
        if (len(@String1) = len(@String2))
        begin
            -- Compare Strings one character at a time until string length is reached
            -- or until the positional characters are not equal.
            set @CharPosition = 1
            set @ReturnValue = 1
            -- Both strings are the same length so use String1 length.
            set @StringsLength = len(@String1)
            while ((@CharPosition <= @StringsLength) and @ReturnValue = 1)
            begin
                if ASCII(SubString(@String1, @CharPosition, 1)) != ASCII(SubString(@String2, @CharPosition, 1))
                    set @ReturnValue = 0
                set @CharPosition = @CharPosition + 1
            end
        end
        else
            -- The strings are not the same length, thus not equal.
            set @ReturnValue = 0
    end
    else
        -- One or both string are null.
        set @ReturnValue = -1
    return @ReturnValue
end
go

/*
Test Code Highlight and run once the function above is installed.

Declare
    @ReturnValue int

-- Test for equality.
set @ReturnValue = dbo.CaseSensitiveCompare('xyz', 'xyz')
Print convert(char(3), @ReturnValue)  

-- Test for inequality.
set @ReturnValue = dbo.CaseSensitiveCompare('xyz', 'Xyz')
Print convert(char(3), @ReturnValue)  

-- Test for inequality.
set @ReturnValue = dbo.CaseSensitiveCompare('xyz', 'xy')
Print convert(char(3), @ReturnValue)  

-- Test for nulls and inequality.
set @ReturnValue = dbo.CaseSensitiveCompare(null, null)
Print convert(char(3), @ReturnValue)  

-- Notice these different empty string are equal due to Sql trimming spaces.
set @ReturnValue = dbo.CaseSensitiveCompare('  ', '')
Print convert(char(3), @ReturnValue)  
*/

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating