sql server (alternate): 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,
Phil

select * 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:
[quoted text, click to view]

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