Solved

SQL to count how many records have lower case name

Posted on 2013-01-31
13
481 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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