How to check for upper-case strings

[SeanAdams]

Hey all,

I was working through a folder full of SQL scripts (studying to renew my SQL server exam), and wanted to separate out the words which are in upper case to make sure that I'd covered all the keywords involved in the exam.

However, after stripping this all back to a stream of unique words - it turned out that checking for upper-case is not a simple function (or am I missing it).

- Tried this with regex - very possible but a bit messy

- Then thought "well - why not apply logic"

     - if a string is equal to it's upper case version, then it's already upper case.   e.g. "CAT" == uppercase("CAT")
     - Unfortunately, I couldn't find how to force Alteryx to do a case-sensitive equality check on two strings - I was getting true with "cat" == "CAT"

     - eventually resorted to a formula which cheats using the StartsWith function which has a case sensitivity argument.    

              StartsWith([InputRow], [UpperInputRow],0) AND  startswith([UpperInputRow],[InputRow],0)

              - This works because if text A starts with text B; AND text B starts with text A, then they are equal.

But there has to be a better way, no?

Aside from using RegEx - is there a simpler way to check if a string is in upper-case; and to case-sensitive-compare two strings for equality?

Thank you

Sean

cc: @ydmuley

[JoeS · Accepted solution]

Hi @SeanAdams

I believe the Regex should be fairly straight forward. If you use:

Regex_Match([Field1],"[A-Z]*",0)

 Within a filter that should return words that are all upper case, the final argument of ",0" is optional and that specifies that it is a case sensitive regex statement.

As you mentioned you wanted to do it without using regex the best way I could think of was to do something similar to what you had done:

1. Create an uppercase field of itself.
2. Append a RecordID
3. Join the stream to itself. One side being the original field the other being the uppercase field, along with the record ID too.

As joins within Alteryx are case sensitive.

I have attached a Workflow with both examples in.

Thanks

Joe

[jdunkerley79 · Accepted solution]

How about a filter with:

CONTAINS(UpperCase([Field1]),[Field1],0)

True will be pure UPPERCASE and False will be others?

[SeanAdams]

you're right @jdunkerley79 - no need to check the reverse because we know we're not dealing with a substring (I had checked in both directions, but since its the same string but with upper, no need).   Feel silly that I didn't spot that :-) 

Thanks @@Joe - didn't think of case sensitivity of joins - that's quite a neat way round too.

[piotrzawistowski]

This is what works for me:

   REGEX_Match(LowerCase([Field1]), [Field1], 0)

If you want to check if the string starts with a capital letter, you can use this condition:

   REGEX_Match(Titlecase([Message]), [Message], 0)

[Neil-HawaiianAir]

I have a slight twist on this question.  I have a column/field that starts out with upper case text and then ends in mixed case.  I would like to parse the upper case into a new column/field and the mixed case into another.  As an example:

THIS WOULD BE ONE COLUMN And this would be another.

Is this possible with REGEX?

Thanks.

[Mchappell67]

Hi, @Neil-HawaiianAir -

I came up with this:

REGEX_replace([Field1], '(^[A-Z\s\W]+)([a-z\s\W]+$)', '\1|\2',0)

Hope that works/helps.

Mark