Select Upper Case text

[hayleigh]

Hi all,

Hopefully this is a simple request.

I have a table that contains place names in lower and upper case, I
want to select all those that are Upper Case, is there some simple SQL
to do this?

Thanks,
Hayleigh

[Eric Blasenheim]

If you really know that the names are either all lower or all upper, a
simple query which really just looks at the first character will
suffice.
Select * from table_name where asc(columnInQuestion) between 65 and 90

This will return all the records whose first character is a capital A
through Z.

97 through 122 for lower case. Non-ASCII characters (e.g., German,
French, Spanish, Danish, Swedish ) are a different story.

Rather than remember the numbers you can also do it this way !

select * from table_name where asc(columnInQuestion) between asc("A")
and asc("Z")

Eric

[Spencer Simpson]

If you don't really know that the names are either all lower case or all
upper case, it's a little more complicated, but still possible, since the
LIKE function (not the LIKE operator) is case sensitive.

So, to select all upper-case text, you might try

Select * from mytable
where LIKE(mycolumn, ucase$(mycolumn), "\")

It's not particularly robust, since your column may hold values with "%" or
"_" characters.

You could write a function in MapBasic to insert "\" characters before any %
and _ characters and use the function in a SQL query in a MapBasic app.
But you should base your decision to do that on how likely it is those
characters will appear in your data.
________________________________

Spencer

Eric

--
You received this message because you are subscribed to the Google Groups
"MapInfo-L" group.
To post to this group, send email to mapi...@googlegroups.com.
To unsubscribe from this group, send email to
mapinfo-l+...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/mapinfo-l?hl=en.

[Warren Vick]

Hello Hayleigh,

As you probably already know, the "=" operator is not case sensitive in MapInfo Pro. However, there is a function called StringCompare(Str1, Str2) which returns 0 if the strings are equal with case sensitivity. You can combine this with the ucase$() function which forces a string to uppercase.

So, your where clause to return only the uppercase entries would be:

StringCompare(YourField, ucase$(YourField)) = 0

Regards,
Warren Vick
Europa Technologies Ltd.
http://www.europa.uk.com

Hi all,

Thanks,
Hayleigh

--

[hayleigh]

Brilliant!! I couldn't get Erics way to work, but both Spencers and
Warrens worked wonderfully!!

Thanks very much!
Hayleigh

[Eric Blasenheim]

Not sure why mine would not work but their solutions in your case were
better; simpler and more complete. So I withdraw mine. StringCompare()
should be a bit faster than Like() for large data sets just because a
binary string comparison is very fast.

Eric Blasenheim
PBBI (MapInfo)

[Spencer Simpson]

Warren's solution will work all the time (and, as Eric said, may be faster);
so use StringCompare() for this instead of Like().
________________________________

Spencer

-----Original Message-----
From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On
Behalf Of hayleigh

Sent: Monday, March 22, 2010 6:35 AM
To: MapInfo-L
Subject: [MI-L] Re: Select Upper Case text

Thanks very much!
Hayleigh

--