1. Resources
    2. SQL Server

Case Sensitive search with SQL

Posted Date: 27 Dec 2011 |Updated: 27-Dec-2011 |Category: SQL Server |Author: |Member Level: Gold |Points: 15 |


This article gives you idea about how to do Case Sensitive search with MSSQL. Many times we need to do Case Sensitive search in database. This article will drive you through all Queries need to do Case Sensitive search. Find Case Sensitive search with SQL.

Learn Case Sensitive search with SQL


Introduction


last week our senior told me to select records from database starts with 'r' (small R). it troubles a lot, and we got solution finally.

may be there are some situation where you need to find case sensitive records from database.

How to do that?


To achive this task we need to take help of SQL Collation.


What is SQL Collation ?


A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet

such as Latin1_General (the Latin alphabet used by western European languages).

default collation is Latin.

Get into action


suppose I have a table named 'users' contains 4 records.

1. responsive

2. Responsive

3. RESPONSIVE

4. ResPONsive


if we fire Query "select * from users where name = 'responsive''
it will return me all columns cause all column contains same data and search is not case Sensitive.

select



To fetch case sensitive records you need to change collation of that column.
Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.
here is syntax 

SELECT Column1 FROM Table1 WHERE Column1 = 'expression'

here we go

collate



by above method, we change column collate for temporary use. but we can change it's collate permanently. by using following Query


ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS


To know the collation of the column for any table run following Stored Procedure
EXEC sp_help tableName


here is the result

stored procedure



Thanks
This simple article gives you a short idea of casesensitive search in database.
Suggestion are most welcome

Thanks
koolprasad2003


Comments

Author: Alwyn Duraisingh03 Jan 2012 Member Level: Gold   Points : 2

We can also use VarBinary conversion to search using case-sensitive


CREATE TABLE #Test
(
	Name VARCHAR(100)
)

INSERT INTO #Test
SELECT 'Alwyn'
UNION ALL SELECT 'ALWYN'
UNION ALL SELECT 'alwyn'
UNION ALL SELECT 'ALwyn'

SELECT * FROM #Test

DECLARE @Name VARCHAR(100)
SET @Name = 'Alwyn'
SELECT Name AS Result 
FROM #Test 
WHERE CONVERT(VARBINARY, Name) = CONVERT(VARBINARY, @Name)

DROP TABLE #Test

Author: KUNDAN KUMAR SRIVASTAVA03 Jan 2012 Member Level: Gold   Points : 0

Thanks to both for posting such an useful resource.

Keep posting more and more useful resource.

Author: maruthanayakam21 Feb 2014 Member Level: Silver   Points : 5

SQL SERVER – Collate – Case Sensitive SQL Query Search
Case Sensitive SQL Query Search

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh', following statement will return you all the four records.

SELECT Column1
FROM Table1
WHERE Column1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch") from above query, the collation of the query needs to be changed as follows.

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.

EXEC sp_help DatabaseName

Second results set above script will return you collation of database DatabaseName.

Regards,
Marudhu...



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
    • Name:
    Email:
    Submit Article
    Return to Article Index

    Top Contributors

    Today
      Last 7 Days

        more...

        About dotnetspider.com

        We believe in providing quality content to our readers. If you have any questions or concerns regarding any content published here, feel free to contact us using the Contact link below.

        Quick Links
        General
        Services

        Digital Marketing by SpiderWorks Technologies, Kochi - India. ©