How do I get uppercase values returned only.

I have a requirement to get only upper case letters form the table.

DECLARE @CountryNames TABLE (Name VARCHAR(20))

INSERT INTO @CountryNames VALUES ('US')
INSERT INTO @CountryNames VALUES ('India')
INSERT INTO @CountryNames VALUES ('uk')
INSERT INTO @CountryNames VALUES ('AU')

Get all the records

SELECT  *  FROM  @CountryNames 

 

Get records which are starting the Capital letter

SELECT  *  FROM  @CountryNames   

WHERE  ASCII(Name) = (ASCII(UPPER(Name)))

Get Records total word is captial

SELECT     Name AS Country
FROM         @CountryNames
WHERE     (Name COLLATE Finnish_Swedish_CS_AS = UPPER(Name) COLLATE Finnish_Swedish_CS_AS)