query only records written in all caps

I have a database of 5,000 records of names.  some names are written in caps.  some records have an "XXX" or "xxx" at the end of the name field.

How would I write a query that sorts out the names written in CAPS?

Additionally, how would I write a querry that sorts out all the records ending in "xxx" or "XXX"?

Thanks,
mkobeyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

josephwalshCommented:
I do not follow the first part of your question.
Can you give an example ?

2nd half
"SELECT columnlist FROM tablename WHERE columnname like *XXX"
0
satheeshmCommented:
write a user defined function to check for all CAPS

a)SELECT CheckIfAllCaps
(name) from  table1
order by checkcaps(name)
union
SELECT name FROM tablename WHERE right(name, 3) = "xxx"

UserDefined Function (you can find this userdefined function here : http://www.vikramlakhotia.com/Checking_if_all_the_character_in_a_string_are_capital_in_SQL.aspx)

CREATE FUNCTION CheckIfAllCaps (@StringToCheck VARCHAR(500))
RETURNS bit
AS
BEGIN
   DECLARE @return BIT
   DECLARE @position INT

   SET @position = 1

   WHILE @position <= DATALENGTH(@StringToCheck)
   BEGIN
       IF ASCII(SUBSTRINGTOCHECK(@StringToCheck, @position, 1)) BETWEEN 65 AND 90  
           SELECT @return = 0
       ELSE
           SELECT @return = 1

       IF @Return <> 1
           SET @position = @position + 1
       ELSE
           GOTO ExitUDF
END

ExitUDF:
RETURN @return

END
0
mkobeyAuthor Commented:
some records are written in "lower case" while others are written in "uppercase."  I want to sort out the ones that are written in uppercase.

on the second half, it is not working.  the field contains "XXX" but also contains other text as well.  I would like to do the same with "???" and I know question marks are treated differently.

Thanks for helping this noob.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

josephwalshCommented:
Part 1
Do you just want to extract the records that are uppercase?/Lowercase?
0
mkobeyAuthor Commented:
yes.  extract only the uppercase records
0
satheeshmCommented:
Hi mkobey,

Did you see my solution, you dont have a way to find uppercase, you have to check ascii of each char in string. so in my solution first part returns only upper case letters and second part returns only strings ends with "xxx".

satheesh
0
josephwalshCommented:
Try the following :
This returns results where the data is all uppercase

SELECT PersonName FROM tblTableName
WHERE StrComp(UCase([PersonName]),[PersonName],0)=0;
0
mkobeyAuthor Commented:
Thanks for your help.  I have the first part solved but cannot get the ucase problem solved.  I have attached a sample mdb.  The goal is to sort out only the records that are written in ALL CAPS.  See the query "QryCaps"

Thanks again.
db1.mdb
0
satheeshmCommented:
see this..

SELECT Name FROM test
WHERE StrComp(UCase([Name]),[Name],0)=0 or right(name,3) = "xxx"

check attached file

Database1.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mkobeyAuthor Commented:
Thanks for the help.  I could not get the syntax right.

Regards
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.