CODEWITHSUNDEEP
sqlsql-server
Hotlansy Httlandy
Hotlansy Httlandy

Reputation: 349

SELECT Only the words that are capitalized

I have a column called Cities, this column has data as the following:

 City 1
 CITY 1         
 CITY 1

I want to Select only the CITY (Capitalized)

This is not doing the job:

SELECT * FROM Location
WHERE Cities LIKE 'CITY 1'

This is giving me all the CITY 1 includes City 1

Upvotes: 1

Views: 209

Answers (6)

Mason T.
Mason T.

Reputation: 1577

Try this query.

SELECT *
FROM Location
WHERE Cities LIKE 'CITY 1'
COLLATE Latin1_General_CS_AS;

The collate makes the query case sensitive 'CS' and accent sensitive 'AS'. The Latin1 specifies English among many other languages.

Upvotes: -1

Dave Sexton
Dave Sexton

Reputation: 11188

Try:

SELECT *
FROM Location
WHERE Cities COLLATE Latin1_General_CS_AS = 'CITY 1'

Upvotes: 0

Iqbal Hossain
Iqbal Hossain

Reputation: 117

Solution:

Select * From Location Where Cities=BINARY UPPER(Cities);

Upvotes: -1

Chris Trahey
Chris Trahey

Reputation: 18290

You can use the BINARY operator to enforce case sensitivity.

http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

SELECT * FROM Location WHERE BINARY Cities = 'CITY 1';

Upvotes: 0

Charleh
Charleh

Reputation: 14002

I assume your SQL is in a non case-sensitive collation in which case a search for 'C' and 'c' will return the same result. You need to do a case-sensitive search by collating the query:

SELECT * FROM Location WHERE Cities LIKE 'CITY 1' COLLATE <Insert case sensitive collation here>

e.g.

SELECT * FROM Location WHERE Cities LIKE 'CITY 1' COLLATE Latin1_General_CS_AI

Upvotes: 1

podiluska
podiluska

Reputation: 51494

By default, SQL Server will use a case insensitive collation.

SELECT * 
FROM Location 
WHERE Cities LIKE 'CITY 1' 
COLLATE Latin1_General_CS_AI

Mark your comparison operator with a case-sensitive collation to apply a case sensitive filter

Upvotes: 9

Related Questions