Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL to count how many records have lower case name

Posted on 2013-01-31
13
Medium Priority
?
493 Views
Last Modified: 2013-02-06
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
Comment
Question by:dyarosh
  • 6
  • 6
13 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38840728
Howdy...

That's pretty straight forward.


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



Good Luck,
Kent
0
 

Author Comment

by:dyarosh
ID: 38840817
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
 

Author Comment

by:dyarosh
ID: 38840826
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38840849
That seems really odd.

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

Author Comment

by:dyarosh
ID: 38840886
There are over 120K rows in the table.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38840917
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38840928
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
 

Expert Comment

by:smokinmark
ID: 38841325
SELECT COUNT(*)
FROM [databasename].[dbo].[Proof_Policy]
WHERE ( TRIM(LASTNAME) <> UPPER(TRIM(LASTNAME)) )
0
 

Author Comment

by:dyarosh
ID: 38841451
That isn't working either.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38841503
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
 

Accepted Solution

by:
dyarosh earned 0 total points
ID: 38843432
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38843839
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
 

Author Closing Comment

by:dyarosh
ID: 38858681
I ended up finding the solution from other posts on the web.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question