Upper and LOWER | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Upper and LOWER

Hey guys- I am trying to find out which records in a field have lowercases where they should be all capital letters. Example-
Select * from Company where Name<>Upper(Name)
This returns no recrods. I am assuming because the characters match (regardless of capitalization). How can I accomplish finding those entries that are no all caps? Thanks for any suggestions.
Add a COLLATE clause that ‘casts’ the column to a case-sensitive collation (_CS_ instead of _CI_).
CREATE TABLE #a (col varchar(10) COLLATE SQL_Latin1_General_Cp1_CI_AS) INSERT INTO #a VALUES (‘a’)
WHERE UPPER(col) <> col COLLATE SQL_Latin1_General_Cp1_CS_AS SELECT * FROM #a
WHERE UPPER(col) <> col drop table #a col
a col

Other method
declare @t table(data varchar(100))
insert into @t
select ‘test’ union all
select ‘TEST’
select data from @t where cast(data as varbinary(100))=cast(upper(data) as varbinary(100)) Result (2 row(s) affected) data
TEST (1 row(s) affected)
Madhivanan Failing to plan is Planning to fail
Madhivanan, that doesn’t work when you have data like<br /><pre id="code"><font face="courier" size="2" id="code"><br />union all select ‘Test'<br /></font id="code"></pre id="code"><br />Btw, Jeffrey is after Lower-case data, so the query should have been<br /><pre id="code"><font face="courier" size="2" id="code"><br />where cast(data as varbinary(100))<b>!</b>=cast(upper(data) as varbinary(100))<br /></font id="code"></pre id="code"><br /><br />Burn-out syndrom lately?[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
Thanks for the correction. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Thanks very much guys, I appreciate it!~