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’)
INSERT INTO #a VALUES (‘B’) SELECT * FROM #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 <pre id="code"> union all select ‘Test' </pre id="code"> Btw, Jeffrey is after Lower-case data, so the query should have been <pre id="code"> where cast(data as varbinary(100))!=cast(upper(data) as varbinary(100)) </pre id="code"> Burn-out syndrom lately?[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> 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=’

‘ />] Madhivanan Failing to plan is Planning to fail

Thanks very much guys, I appreciate it!~

]]>