Solved

SQL to count how many records have lower case name

Posted on 2013-01-31
13
467 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 45

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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 45

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 45

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 45

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 45

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 45

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

Suggested Solutions

Title # Comments Views Activity
SQL Query Syntax:  Why is the CTE Pulling in More Data Than Asked For? 5 73
Analysis of table use 7 62
How can I get this column in my query? 2 56
Query to return total 6 24
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 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