Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL SERVER Case Sensitive SQL Query Search using Collate

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 222
    Comment on it

    SQL SERVER – Case Sensitive SQL query search using Collate

     


    SQL SERVER is not case sensitive therefore there are scenarios in which SQL query search may provide same result for various inputs which only differ with respect to capital and small letter. In such cases Collate clause should be used.

     

    For example :-

     

    Consider tblLogin Table with following structure :-

     

     

    The above table contain following information :-

     

     

    Create a stored procedure to get UserName based on passed UserEmail and Password

     

    Alter PROCEDURE sp_CheckUserLogin
          -- Adding parameters for the stored procedure
          @Email varchar(50),
          @Pwd varchar(50)
    AS
    BEGIN
         
        -- Select User Details from tblLogin Table
          select UserName from tblLogin where [UserEmail] = @Email and [Password] = @Pwd
         
    END

     

    Execute above stored procedure with following statements:

    exec sp_CheckUserLoginCredential 'suraj@gmail.com','SuraJ@123'
    
    exec sp_CheckUserLoginCredential 'suraj@gmail.com','Suraj@123'

     

    In above statements output will remain same,it does not matter password passed is in capital or small letter or any combination of small and capital letter.

    Output :

     

    This should not happen,to avoid this collate clause should be used :

     

    Alter the procedure sp_CheckUserLogin by adding Collate clause

     

    
    Alter PROCEDURE sp_CheckUserLogin
          --  Adding parameters for the stored procedure
          @Email varchar(50),
          @Pwd varchar(50)
    AS
    BEGIN
    
        -- Select User Details from tblLogin Table
          select UserName from tblLogin where [UserEmail] = @Email and [Password] = @Pwd COLLATE Latin1_General_CS_AS
         
    END

     


    Now executing stored procedure will only give result for case sensitive password.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: