Thursday, August 16, 2007

[sql] Detecting / Filtering All Caps Text

Needed to detect if a field contained all caps / uppercase text. Once again, Yodakawa comes to the rescue.

-- Retrieve rows where FooText is not all caps
--(using it in where clause)
select top 100 FooText, * from FooTable
where FooText like '%[abcdefghijklmnopqrstuvwxyz]%'
COLLATE SQL_Latin1_General_CP1_CS_AS

-- Syntax for using it in the select
select top 100
isnull((case when FooText like '%[abcdefghijklmnopqrstuvwxyz]%'
COLLATE SQL_Latin1_General_CP1_CS_AS
then description2 else '' end), '')
from FooTable

2 comments:

  1. Or:

    select top 100 FooText, * from FooTable
    where UPPER(FooText) COLLATE SQL_Latin1_General_CP1_CS_AS <> FooText COLLATE SQL_Latin1_General_CP1_CS_AS

    ReplyDelete
  2. Should be:
    COLLATE SQL_Latin1_General_CP1_CS_AS = FooText COLLATE SQL_Latin1_General_CP1_CS_AS
    To show data in UPPERCASE.

    Using:
    COLLATE SQL_Latin1_General_CP1_CS_AS <> FooText COLLATE SQL_Latin1_General_CP1_CS_AS
    Shows data NOT in UPPERCASE

    ReplyDelete