asked on

SQL to count how many records have lower case name

I have a table that is populated by a form that a user can enter in one of two ways. The first way is when the user pastes the data from another system. When done this way (which is the preferred way), the data goes into the columns in upper case. The other way is for the user to manually enter the data and when this happens the data usually appears in lower case.

What I am looking for is a query that will count how many records where the LastName column contains lower case letters.

Can anyone help me with this?

Kent Olsen

Howdy...

That's pretty straight forward.

SELECT count(*)
FROM mytable
WHERE lastname <> upper (lastname);

Good Luck,
Kent

dyarosh

ASKER

I tried your query and it returned 0. Here is what I have:

SELECT COUNT(*)
FROM [databasename].[dbo].[Proof_Policy]
WHERE LastName <> UPPER(LastName)

dyarosh

ASKER

Also, the following queries returned the same count:

SELECT COUNT(*)
FROM [databasename].[dbo].[Proof_Policy]
WHERE LastName = UPPER('Smith')

---------------------------------------------------------------------
SELECT COUNT(*)
FROM [databasename].[dbo].[Proof_Policy]
WHERE LastName = LOWER('Smith')

Kent Olsen

That seems really odd.

How many rows are in the table? Can you show a few lines?

dyarosh

ASKER

There are over 120K rows in the table.

Kent Olsen

Let's find out a bit more.

SELECT COUNT(*)
FROM [databasename].[dbo].[Proof_Policy];

SELECT COUNT(*)
FROM [databasename].[dbo].[Proof_Policy]
WHERE LastName = UPPER (LastName);

SELECT COUNT(*)
FROM [databasename].[dbo].[Proof_Policy]
WHERE LastName = LOWER (LastName);

And how are you connecting to the database?

Kent Olsen

I wonder if there are trailing spaces in the data? That can happen with some data connections and drivers.

SELECT count(*)
FROM [databasename].[dbo].[Proof_Policy]
WHERE LastName <> RTRIM (LastName);

smokinmark

SELECT COUNT(*)
FROM [databasename].[dbo].[Proof_Policy]
WHERE ( TRIM(LASTNAME) <> UPPER(TRIM(LASTNAME)) )

dyarosh

ASKER

That isn't working either.

Kent Olsen

Maybe you've got a weird collation in use.

Try these two queries. We'll use "local" data instead of your stored data.

SELECT count(*)
FROM 
(
  SELECT 'ABCD'
  UNION ALL
  SELECT 'abcd'
  UNION ALL
  SELECT 'ABcd'
) t0 (LastName)
WHERE LastName = UPPER (LastName);

Open in new window

If that query returns 3, your collating sequence is collapsing the case. Recasting to varbinary should solve that.

SELECT LastName
FROM 
(
  SELECT 'ABCD'
  UNION ALL
  SELECT 'abcd'
  UNION ALL
  SELECT 'ABcd'
) t0 (LastName)
WHERE cast (LastName as varbinary) = cast (UPPER (LastName) as varbinary);

Open in new window

ASKER CERTIFIED SOLUTION

dyarosh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kent Olsen

An explanation of what was happening, along with a workable solution, was posted in this thread. The solution shown by the OP is not substantially different.

dyarosh

ASKER

I ended up finding the solution from other posts on the web.