Case-Sensitive String Comarison on Any SQL Server

,

When you want to compare two strings that may have case differences on a case-insensitive server, this script will quickly and easily do the compare for you.  It returns 0 if strings are equal, 1 if not.  Feel free to modify/reuse in your code.

/*

                            ©Copyright 2003  Mike McManus (mmcmanus@cierant.com) All Rights Reserved


*/

/* ---------------------   For case-sensitive compare of string values on case-insensitive SQL Server  -------------------------- */
/* ---------------------------------  Will Return 0 if parms are equal, 1 if not   ---------------------------------------------- */

Create Procedure CaseCompare @String1 Varchar(100),@String2 Varchar(100)
As
Declare
        @Pos            int,
        @String1_Ascii  int,
        @String2_Ascii  int,
        @ReturnVal      int,
        @String1_len    int,
        @String2_len    int
/* -------------------------------------------------- Initialize Vars ----------------------------------------------------------- */
Set NOCOUNT On
Set @String1_Ascii = 0
Set @String2_Ascii = 0
Set @Pos = 1
Set @ReturnVal = 0
/* ------------------------------------------------------------------------------------------------------------------------------ */

    If ((@String1 Is Null) Or (@String2 Is Null))
    
        Begin
        
            Select @ReturnVal = 1
        
        End 
    
    Else 
   
    Begin
        
        Select @String1_len = len(@String1), @String2_len = len(@String2)
        
        If (@String1_len != @String2_len) 
            
            Begin
                
                Select @ReturnVal = 1
            
            End
        
        Else 

            Begin
            
            /* ------- Compare Strings one character at a time until finished, or until they don't match --------------------- */
            
            While ((@Pos <= Datalength(@String1)) And (@ReturnVal = 0))
               
            Begin
                   
                   Select @String1_Ascii = ASCII(SubString(@String1, @Pos, 1)), @String2_Ascii = ASCII(SubString(@String2, @Pos, 1))
                   
                   Select @ReturnVal = @ReturnVal + (@String1_Ascii - @String2_Ascii)
                   
                   Set @Pos = @Pos + 1
            
            End
            
       End

   End

 /* --- To make sure we get a non-negative return value --- */
   
    If (@ReturnVal != 0)
        Begin
            Set @ReturnVal = 1
        End
 /* ------------------------------------------------------- */ 

 -- If testing, or running stand-alone, do Select.  To execute from other stored proc or function, do Return.
 -- Select @ReturnVal
   Return @ReturnVal
go

Rate

Share

Share

Rate