[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

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?
0
dyarosh
Asked:
dyarosh
  • 6
  • 6
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Howdy...

That's pretty straight forward.


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



Good Luck,
Kent
0
 
dyaroshAuthor Commented:
I tried your query and it returned 0.  Here is what I have:

SELECT COUNT(*)
FROM [databasename].[dbo].[Proof_Policy]
WHERE LastName <> UPPER(LastName)
0
 
dyaroshAuthor Commented:
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')
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Kent OlsenData Warehouse Architect / DBACommented:
That seems really odd.

How many rows are in the table?  Can you show a few lines?
0
 
dyaroshAuthor Commented:
There are over 120K rows in the table.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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);
0
 
smokinmarkCommented:
SELECT COUNT(*)
FROM [databasename].[dbo].[Proof_Policy]
WHERE ( TRIM(LASTNAME) <> UPPER(TRIM(LASTNAME)) )
0
 
dyaroshAuthor Commented:
That isn't working either.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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

0
 
dyaroshAuthor Commented:
I ended up using this query to give me the number of rows whose LastName is in UPPER Case.  I then subtracted that number from the total number of rows in the table to get the number I was looking for.

SELECT COUNT(T1.LastName)
FROM [AigDirectNet].[dbo].[Proof_Policy] T1
JOIN [AigDirectNet].[dbo].[Proof_Policy] T2
ON T1.PolicyID = T2.PolicyID
AND UPPER(T1.LastName) = T2.LastName COLLATE Latin1_General_CS_AS
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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.
0
 
dyaroshAuthor Commented:
I ended up finding the solution from other posts on the web.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now