Trying to match lowercase

27 Jul 2006 2:14 PM

Stephen Howe

Hi

Using SQL Server 2000, SP4

On our database currently we have some bad data and it turns out the only
distinguishing feature is that bad records
never contains lowercase character - only uppercase, numbers, punctiuation
characters.

The field in question is a SQL Server fixed-length ASCII char type, 50
characters

How do I show records [not] containing lower-case characters?

If I do

SELECT * FROM sometable where title LIKE '%[a-z]%'

this does not work. It also shows records with uppercase characters despite
the fact that '%[a-z]%' was supposed to match just on lowercase. Yet on
doing

SELECT * FROM sometable

I can see titles which are in uppercase, the rest mixedcase.

Thanks

Stephen Howe

27 Jul 2006 2:29 PM

Anith Sen

Lookup COLLATE in SQL Server Books Online. You should be able to use a case
sensitive collation in your comparison phrase.

Another way of doing it is to convert the arguments to VARBINARY. Search the
archives of this newsgroup and you should find plenty of examples.

--
Anith

27 Jul 2006 2:32 PM

Aaron Bertrand [SQL Server MVP]

The query is case insensitive because you are on a case-insensitive
collation. Assuming you are using SQL_Latin1_General_CP1_CI_AS, try this:

CREATE TABLE #foo
(
s VARCHAR(32)
)

SET NOCOUNT ON;

INSERT #foo SELECT 'foo'
INSERT #foo SELECT 'BAR'
INSERT #foo SELECT 'BaR'

-- those that contain at least 1 lower-case character
SELECT * FROM #foo WHERE s COLLATE SQL_Latin1_General_CP1_CS_AS != UPPER(s)
COLLATE SQL_Latin1_General_CP1_CS_AS

-- those that contain no lower-case characters
SELECT * FROM #foo WHERE s COLLATE SQL_Latin1_General_CP1_CS_AS = UPPER(s)
COLLATE SQL_Latin1_General_CP1_CS_AS

DROP TABLE #foo

Show quote

"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message
news:%23IlBNcYsGHA.1224@TK2MSFTNGP03.phx.gbl...
> Hi
>
> Using SQL Server 2000, SP4
>
> On our database currently we have some bad data and it turns out the only
> distinguishing feature is that bad records
> never contains lowercase character - only uppercase, numbers, punctiuation
> characters.
>
> The field in question is a SQL Server fixed-length ASCII char type, 50
> characters
>
> How do I show records [not] containing lower-case characters?
>
> If I do
>
> SELECT * FROM sometable where title LIKE '%[a-z]%'
>
> this does not work. It also shows records with uppercase characters
> despite
> the fact that '%[a-z]%' was supposed to match just on lowercase. Yet on
> doing
>
> SELECT * FROM sometable
>
> I can see titles which are in uppercase, the rest mixedcase.
>
> Thanks
>
> Stephen Howe
>
>

27 Jul 2006 2:47 PM

Aaron Bertrand [SQL Server MVP]

> -- those that contain at least 1 lower-case character
> SELECT * FROM #foo WHERE s COLLATE SQL_Latin1_General_CP1_CS_AS !=
> UPPER(s) COLLATE SQL_Latin1_General_CP1_CS_AS
>
> -- those that contain no lower-case characters
> SELECT * FROM #foo WHERE s COLLATE SQL_Latin1_General_CP1_CS_AS = UPPER(s)
> COLLATE SQL_Latin1_General_CP1_CS_AS

Of course, the second COLLATE is unnecessary to achieve the same results.

27 Jul 2006 2:36 PM

Omnibuzz

Check out this link
http://omnibuzz-sql.blogspot.com/2006/06/case-sensitive-search-in-sql-server.html

Hope this helps..

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

27 Jul 2006 2:40 PM

Arnie Rowland

The default 'collation' is case-insensitive -meaning that the server sees no difference between upper and lower case letters.

However, in order to find the 'bad data' you can add a case-sensitivity directive to the search query.

SELECT *
FROM SomeTable
WHERE Title COLLATE Latin1_General_CS_AS NOT LIKE lower(Title)

The where clause allows the use of a case sensitivity directive.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message news:%23IlBNcYsGHA.1224@TK2MSFTNGP03.phx.gbl...
> Hi
>
> Using SQL Server 2000, SP4
>
> On our database currently we have some bad data and it turns out the only
> distinguishing feature is that bad records
> never contains lowercase character - only uppercase, numbers, punctiuation
> characters.
>
> The field in question is a SQL Server fixed-length ASCII char type, 50
> characters
>
> How do I show records [not] containing lower-case characters?
>
> If I do
>
> SELECT * FROM sometable where title LIKE '%[a-z]%'
>
> this does not work. It also shows records with uppercase characters despite
> the fact that '%[a-z]%' was supposed to match just on lowercase. Yet on
> doing
>
> SELECT * FROM sometable
>
> I can see titles which are in uppercase, the rest mixedcase.
>
> Thanks
>
> Stephen Howe
>
>

27 Jul 2006 2:53 PM

Stephen Howe

Thanks everyone.
I did get further on my own steam before seeing everyones answers.

Cheers

Stephen Howe

27 Jul 2006 3:13 PM

Stephen Howe

SELECT *
FROM   SomeTable
WHERE Title COLLATE Latin1_General_CS_AS NOT LIKE lower(Title)

I can "see" my bad data now

The bad data is

SELECT *
FROM   SomeTable
WHERE Title COLLATE Latin1_General_CS_AS = UPPER(Title)

and the good data is

SELECT *
FROM   SomeTable
WHERE Title COLLATE Latin1_General_CS_AS <> UPPER(Title)

Thanks

Stephen Howe

27 Jul 2006 3:30 PM

Arnie Rowland

Correction. The previous only locates rows that have upper case characters. This correction should find rows with lower case alpha characters AND non-alpha characters.

SELECT *
FROM SomeTable
WHERE ( Title COLLATE Latin1_General_CS_AS <> lower( Title )
OR patindex( '%[^a-z]%', Title ) <> 0
)

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"Arnie Rowland" <ar***@1568.com> wrote in message news:%23iQXQqYsGHA.4080@TK2MSFTNGP03.phx.gbl...

The default 'collation' is case-insensitive -meaning that the server sees no difference between upper and lower case letters.

However, in order to find the 'bad data' you can add a case-sensitivity directive to the search query.

SELECT *
FROM SomeTable
WHERE Title COLLATE Latin1_General_CS_AS NOT LIKE lower(Title)

The where clause allows the use of a case sensitivity directive.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message news:%23IlBNcYsGHA.1224@TK2MSFTNGP03.phx.gbl...
> Hi
>
> Using SQL Server 2000, SP4
>
> On our database currently we have some bad data and it turns out the only
> distinguishing feature is that bad records
> never contains lowercase character - only uppercase, numbers, punctiuation
> characters.
>
> The field in question is a SQL Server fixed-length ASCII char type, 50
> characters
>
> How do I show records [not] containing lower-case characters?
>
> If I do
>
> SELECT * FROM sometable where title LIKE '%[a-z]%'
>
> this does not work. It also shows records with uppercase characters despite
> the fact that '%[a-z]%' was supposed to match just on lowercase. Yet on
> doing
>
> SELECT * FROM sometable
>
> I can see titles which are in uppercase, the rest mixedcase.
>
> Thanks
>
> Stephen Howe
>
>

27 Jul 2006 2:50 PM

Stephen Howe

Update:
Seems to be a function of collation
And it seems that default collation occurs on installing SQL Server,
creating a database, creating a table, even a field
(correct me if my understanding is wrong)

So all that needs doing is changing the collation for the field
This is SQL_Latin1_General_CP1_CI_AS

I wonder what those CI and AS appended are?
CI looks like Case insensitive because I see collations with CS as an
alternative

Stephen Howe

27 Jul 2006 2:56 PM

Aaron Bertrand [SQL Server MVP]

> I wonder what those CI and AS appended are?
> CI looks like Case insensitive because I see collations with CS as an
> alternative

CI = Case Insensitive
CS = Case Sensitive

AI = Accent Insensitive
AS = Accent Sensitive