CODEWITHSUNDEEP
sqlregexsql-server-2008
Jai
Jai

Reputation: 33

SQL Server 2008: How to select all rows where field has all capital letters?

Here is what I was thinking, I know that in this field, the first word will always be at least 2 characters long.

Select *  
From Table!
where SUBSTRING(Name, 1, 3) like '[A-Z]'

However, this is bringing back non capital letters any ideas?

Upvotes: 3

Views: 8534

Answers (5)

user556674
user556674

Reputation: 

CREATE TABLE #table1
(
x VARCHAR(32)
);

INSERT #table1 SELECT '123aaa';
INSERT #table1 SELECT 'foo';
INSERT #table1 SELECT 'BaR';
INSERT #table1 SELECT 'saM';
INSERT #table1 SELECT 'Sam';
INSERT #table1 SELECT 'SaM';
INSERT #table1 SELECT 'SAM';

SELECT * FROM #table1
WHERE CONVERT(VARBINARY(32), x) = CONVERT(VARBINARY(32), UPPER(x))

DROP TABLE #table1;

Output:

x
SAM

Upvotes: 1

Thomas
Thomas

Reputation: 64674

Select ...
From MyTable
Where Name Not Like '%[^A-Z]%' Collate SQL_Latin1_General_CP1_CS_AS

It should be noted that this will also exclude numbers and characters outside A-Z. If you wanted non-Latin upper case characters included, you really need to use the Upper function along with a Collate predicate:

Select ...
From MyTable
Where Name = Upper(Name) Collate SQL_Latin1_General_CP1_CS_AS

Test script:

With TestData As
    (
    Select '012324' As Name
    Union All Select 'ABC'
    Union All Select 'abc'
    Union All Select 'aBc'
    Union All Select 'ABé'
    Union All Select 'ABÉ'
    )
Select *
From TestData
Where Name = UPPER(Name) Collate SQL_Latin1_General_CP1_CS_AS

Results:

012324
ABC
ABÉ

Upvotes: 6

Jeff Swensen
Jeff Swensen

Reputation: 3573

How about testing capitalized version against original:

SELECT * FROM Table WHERE UPPER(Name) = Name

Edit: As pointed out in the comments this doesn't work if the collation is case-insensitive (which it is by default I think). Check out Thomas's answer.

Upvotes: 0

John K.
John K.

Reputation: 5474

Could you just do something like 

select * from Table!
where Name = upper(Name)

Upvotes: 0

Bobby D
Bobby D

Reputation: 2159

You are going to have to add a collation to your query. This is a good example of how to do it.

Upvotes: 0

Related Questions