How to find SQL fields in all CAPS

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?  
lulichkAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
EvilPostItConnect With a Mentor Commented:
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
 
käµfm³d 👽Commented:

SELECT *
FROM tblName
WHERE column_name = UPPER(column_name)

Open in new window

0
 
pssandhuCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
pssandhuCommented:
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
 
lulichkAuthor Commented:
Thanks for your responses.  This is my first time I've posted a question and I'm very grateful for the help.  
0
 
EvilPostItCommented:
No probs, let me know if i can further assist.
0
All Courses

From novice to tech pro — start learning today.