Solved

SQL to count how many records have lower case name

Posted on 2013-01-31
13
460 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:Kdo
Comment Utility
Howdy...

That's pretty straight forward.


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



Good Luck,
Kent
0
 

Author Comment

by:dyarosh
Comment Utility
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
Comment Utility
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
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
That seems really odd.

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

Author Comment

by:dyarosh
Comment Utility
There are over 120K rows in the table.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
Comment Utility
SELECT COUNT(*)
FROM [databasename].[dbo].[Proof_Policy]
WHERE ( TRIM(LASTNAME) <> UPPER(TRIM(LASTNAME)) )
0
 

Author Comment

by:dyarosh
Comment Utility
That isn't working either.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
Comment Utility
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:Kdo
Comment Utility
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
Comment Utility
I ended up finding the solution from other posts on the web.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now