retrieve upper case data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

retrieve upper case data

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-08-03 : 19:03:05

Hi friends
I have a table (one column staffid) with data like following
staffid
------
AA
Ben
Bev
BW
Carri
CHRIS
how can i write query that returns staffids in upper case?
i mean AA,BW,CHRIS staffids

Cheers

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-03 : 19:18:45

SELECT UPPER(staffid) FROM table

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-08-03 : 19:24:42

sorry seems i asked wrong question.
i want staffids that r in uppercase only.
if u see my data in the previous not all staffids in upper case
i mean my query should return AA,BW,CHRIS staffids only

Cheers

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-03 : 19:39:17

You can use tell SQL to use a case sensitive collation to make the comparison:

select * from mytable
where staffid COLLATE Latin1_General_BIN = upper(staffid)

Or for a less efficient method:

select * from mytable
where BINARY_CHECKSUM(Upper(staffid)) = BINARY_CHECKSUM(staffid)

(Sometimes you can not mix collation sets within a single query and Binary_checksum will always work but it is slower)

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-08-03 : 19:43:14

Ken you are a Genious.
Both queries worked beautifully.
Thank you very much

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources