Solved

How to find SQL fields in all CAPS

Posted on 2010-11-24
6
844 Views
Last Modified: 2012-05-10
One of the tables in my SQL database has a field that is, at times, being populated by a third-party software program.  It is populating this field in all caps.  Is there a way to do a query of this field to find instances where entire words are in all caps?  
0
Comment
Question by:lulichk
6 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34209321

SELECT *
FROM tblName
WHERE column_name = UPPER(column_name)

Open in new window

0
 
LVL 17

Expert Comment

by:pssandhu
ID: 34209381
Actually it depends on your current sql servers collation. To use a case sensitive coallation, you can use that in your select statement. The following examples will return records from the table where the whole value is in UPPER CASE.

CREATE TABLE #t (n  VARCHAR(20))

insert into #t(n) Values ('TEST')
insert into #t(n) Values ('zing')

Select *
From
(
Select *,
         CASE WHEN n COLLATE SQL_Latin1_General_CP1_CS_AS = LOWER(n) then 1 else 0 END Val
From #t ) m
Where Val = 0

Hope this helps

P.

0
 
LVL 17

Expert Comment

by:pssandhu
ID: 34209427
Oops.. missed one important check. The value in the field could also be in PROPER CASE, meaning the first alphabet of the word could be capitalised and the rest of the body will be in LOWER CASE. So, in this case please refer to this example:
CREATE TABLE #t (n  VARCHAR(20))



insert into #t(n) Values ('Vick')

INSERT INTO #t(n) Values ('TEST')

insert into #t(n) Values ('zing')



Select * 

From

	(

		Select	*, 

				CASE WHEN RIGHT(n, Len(n)-1) COLLATE SQL_Latin1_General_CP1_CS_AS = LOWER(RIGHT(n, Len(n)-1)) then 1 

					 ELSE 0 

				END Val

		From #t 

	) m

Where Val = 0



DROP TABLE #t

Open in new window

0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 16

Accepted Solution

by:
EvilPostIt earned 50 total points
ID: 34211247
If your collation is not case sensitive then you can do the following

SELECT *
FROM tblName
WHERE column_name COLLATE Latin1_General_CS_AS= UPPER(column_name)

Obviously you can change the collation to the case senitive version of the one that you are currently using.
0
 

Author Comment

by:lulichk
ID: 34230639
Thanks for your responses.  This is my first time I've posted a question and I'm very grateful for the help.  
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34230649
No probs, let me know if i can further assist.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql calculate reminders 11 69
SQL Merige returns error code when updating 15 51
How to import SQL 2000 database to SQL 2014 5 98
SQL Query with Sum and Detail rows 2 41
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

24 Experts available now in Live!

Get 1:1 Help Now