Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : How to do case sensitive search?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 76
    Comment on it

    Sometimes during application development we need to do case sensitive search. Let us see how we can do the same in SQL Server.

    For illustration purpose we will use a table called Technology which has a column named Platform.Now let us say column platform has the following values: Dotnet, DotNet, dotnet, DOTNET.

    If we run the below query it will return all the four records.

    SELECT Platform
    FROM Technology 
    WHERE Platform= 'dotnet'
    

    In case we want to make the query case sensitive ( for searching 'DotNet')we need to change the collation of the query.

    SELECT Platform
    FROM Technology 
    WHERE Platform COLLATE Latin1_General_CS_AS = 'DotNet'
    

    Adding COLLATE Latin1_General_CS_AS makes the search query case sensitive.However the default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

    In case we want to change the collation of any column for any table permanently we need to run the below query.

    ALTER TABLE Technology 
    ALTER COLUMN Platform VARCHAR(30)
    COLLATE Latin1_General_CS_AS
    

    For knowing the collation of SQL Server database we can use the below query:

    SELECT DATABASEPROPERTYEX('POC', 'Collation')
    

    For knowing the database column collation we can use the below query:

    SELECT name, collation_name
    FROM sys.columns
    WHERE OBJECT_ID IN (SELECT OBJECT_ID
    FROM sys.objects
    WHERE type = 'U'
    AND name = 'Technology')
    

 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: