How do I get uppercase values returned only.

I have a table where inactive names are lower case and active names are
uppercase. Note: Not my design.

Anyways I want to select all names form this table where the name is
uppercase. I see collate and ASCII pop up in searches but the examples
don't seem usable in queries as much as they were for creating tables
and such.

Thanks,
Philselect * from mytable where name = upper(name)

Joe Weinstein at BEA|||No luck with that

Here is my query
SELECT *, last_nme AS Expr1, first_nme AS Expr2
FROM members
WHERE (last_nme = UPPER(last_nme))
ORDER BY last_nme, first_nme

I still see lower case names.|||I fixed it with this
where ASCII(last_nme) = (ASCII(UPPER(last_nme))

Thanks for the responses.
Phil|||As Phillip found out... this will only work if you have set your
instance of SQL Server set to be case-sensitive. The default
installation makes the instance NOT case-sensitive.

-Tom.|||Phillip (pputzback@.ECommunity.com) writes:
> No luck with that
> Here is my query
> SELECT *, last_nme AS Expr1, first_nme AS Expr2
> FROM members
> WHERE (last_nme = UPPER(last_nme))
> ORDER BY last_nme, first_nme
> I still see lower case names.

This should do it:

SELECT *, last_nme AS Expr1, first_nme AS Expr2
FROM members
WHERE last_nme COLLATE Finnish_Swedish_CS_AS =
UPPER(last_nme) COLLATE Finnish_Swedish_CS_AS
ORDER BY last_nme, first_nme

You may prefer to use something else than Finnish_Swedish. It's the
CS_AS part that is the important.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp