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

How do I force upper case in a select statement

I want all of the columns in a select statement to be converted to upper
case. What is the proper syntax for that?SELECT UPPER(col1), UPPER(col2), UPPER(col3), ...
FROM dbo.YourTable;
"Thirsty Traveler" <nfr@.nospam.com> wrote in message
news:ubO0jhqeGHA.3692@.TK2MSFTNGP03.phx.gbl...
>I want all of the columns in a select statement to be converted to upper
>case. What is the proper syntax for that?
>

How do I find the highest Unique Identifier?

I'd like to know the current value of my uniqueID column before I
create a new record.

Is there a way to find out this value?
It is numeric in my case, but I can't just look for the MAX value,
since some records may have been deleted, and the value for the
uniqueID still stays at the higher value.

Is there a way to read this internally kept value?Stacey,

It sounds like you are using an IDENTITY column; if that's the case,
then you can use IDENT_CURRENT to find the last generated IDENTITY
value.

HTH,
Stu|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. I hope you know never to use
IDENTITY as a key.|||Stu - Thanks!
That was exactly what I was looking for!

And CELKO - I'm sure everybody reading this thread will appreciate your
useful contribution...|||Do NOT assume that the next IDENTITY value will equal IDENT_CURRENT +
1. In a multi-user system you cannot reliably predict the next IDENTITY
value to be inserted. Nor should it be necessary to do so.

Stacey, if you explain your requirement fully I'm sure we can help you
with a better solution.

--
David Portas
SQL Server MVP
--|||Stu (stuart.ainsworth@.gmail.com) writes:
> It sounds like you are using an IDENTITY column; if that's the case,
> then you can use IDENT_CURRENT to find the last generated IDENTITY
> value.

But beware of that IDENT_CURRENT is not safe from other processes. That is,
if you call IDENT_CURRENT before you insert a row, and the call
scope_identity() to see what you actually got, they may not be the same.

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

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

How do I do difference in this case?

I have a customers table that looks like this: custid, name, city
I have a temp table that looks like this: name, city
I want to find all the records in the temp table that do not exists in the
customers table.
There are no nulls or duplicate name/city fields in either table.
If I had a custid field in the temp table I could do a simple outer join and
look for the NULL customer table values to find the temp records that did
not exist in the customers table.
But I can't get this to work matching on the name/city fields.
Can someone help me out with this?SELECT * FROM TempTable AS a
WHERE NOT EXISTS (SELECT * FROM RealTable AS b WHERE b.Name = a.name and
b.city = a.city)
Andrew J. Kelly SQL MVP
"Dave" <dave@.nospam.ru> wrote in message
news:%2308WBS2JFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I have a customers table that looks like this: custid, name, city
> I have a temp table that looks like this: name, city
> I want to find all the records in the temp table that do not exists in the
> customers table.
> There are no nulls or duplicate name/city fields in either table.
> If I had a custid field in the temp table I could do a simple outer join
> and
> look for the NULL customer table values to find the temp records that did
> not exist in the customers table.
> But I can't get this to work matching on the name/city fields.
> Can someone help me out with this?
>

How do I do difference in this case?

I have a customers table that looks like this: custid, name, city
I have a temp table that looks like this: name, city
I want to find all the records in the temp table that do not exists in the
customers table.
There are no nulls or duplicate name/city fields in either table.
If I had a custid field in the temp table I could do a simple outer join and
look for the NULL customer table values to find the temp records that did
not exist in the customers table.
But I can't get this to work matching on the name/city fields.
Can someone help me out with this?SELECT * FROM TempTable AS a
WHERE NOT EXISTS (SELECT * FROM RealTable AS b WHERE b.Name = a.name and
b.city = a.city)
Andrew J. Kelly SQL MVP
"Dave" <dave@.nospam.ru> wrote in message
news:%2308WBS2JFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I have a customers table that looks like this: custid, name, city
> I have a temp table that looks like this: name, city
> I want to find all the records in the temp table that do not exists in the
> customers table.
> There are no nulls or duplicate name/city fields in either table.
> If I had a custid field in the temp table I could do a simple outer join
> and
> look for the NULL customer table values to find the temp records that did
> not exist in the customers table.
> But I can't get this to work matching on the name/city fields.
> Can someone help me out with this?
>

How do I do difference in this case?

I have a customers table that looks like this: custid, name, city
I have a temp table that looks like this: name, city
I want to find all the records in the temp table that do not exists in the
customers table.
There are no nulls or duplicate name/city fields in either table.
If I had a custid field in the temp table I could do a simple outer join and
look for the NULL customer table values to find the temp records that did
not exist in the customers table.
But I can't get this to work matching on the name/city fields.
Can someone help me out with this?
SELECT * FROM TempTable AS a
WHERE NOT EXISTS (SELECT * FROM RealTable AS b WHERE b.Name = a.name and
b.city = a.city)
Andrew J. Kelly SQL MVP
"Dave" <dave@.nospam.ru> wrote in message
news:%2308WBS2JFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I have a customers table that looks like this: custid, name, city
> I have a temp table that looks like this: name, city
> I want to find all the records in the temp table that do not exists in the
> customers table.
> There are no nulls or duplicate name/city fields in either table.
> If I had a custid field in the temp table I could do a simple outer join
> and
> look for the NULL customer table values to find the temp records that did
> not exist in the customers table.
> But I can't get this to work matching on the name/city fields.
> Can someone help me out with this?
>