6 Replies Latest reply on Nov 5, 2018 1:12 PM by Matt Wilson

# Determine if string includes a letter

I need to exclude all strings in my dimension that contain a letter (A-Z). The below formula would work if I ran it all the way through Z, but I would like to find a simpler way.

CONTAINS([Job (Sage)],'A') OR CONTAINS([Job (Sage)],'B')

//then filter as false

Thanks!

• ###### 1. Re: Determine if string includes a letter

Hi Matt,

What is Jobstage looks like actually with a String

• ###### 2. Re: Determine if string includes a letter

Hey Deepak,

108-00-010 - with no letter

108-00-LT1 or 108-PL-001 - With letters. There are a number of different positions where letters might show up in the string.

• ###### 3. Re: Determine if string includes a letter

Hi Matt,

REGEXP_MATCH([Job (Sage)],'[a-zA-Z]') would give you "True" for any string that contains alphabet.

Thanks,

Nawaz

2 of 2 people found this helpful
• ###### 4. Re: Determine if string includes a letter

Matt;

Do you know how many unique non-alpha values might be present?

Rather than searching for 26 (or 52) alpha characters, you might have faster results if you REPLACE the numeric values with blanks, and replace dashes and spaces, too.

This means you might have as few as 11 or 12 passes. In the LENgth of the remaining string is >0, you know an alpha is in there.

This option is probably memory intensive and very messy. I might even find people with torches and pitchforks in front of the building for even suggesting such a monstrosity.

Perhaps another option would be to PARSE the string up into bite-sized snacks (1 alphanumeric per field name) and strain it through the SET of alphas. SETs should be FAST, I imagine, and your formula would simply look for any TRUE value and reject the whole.

This process would work when you have a controlled string length (e.g. always 10 characters). It quickly spins out of control (at least for my rattled old brain) when I think about string of any length.

One other thought: you don't REALLY need to check for A-Z. If you get the ASC value of any string member, you just need to check if it's between A and Z before moving to the next character in the string. I don't know if, ultimately, this is faster or slower than dozens of CONTAIN statements, but I bet you a nickel you could figure it out!

Best of luck!

• ###### 5. Re: Determine if string includes a letter

WOW nawazuddin mohammed: I am TOTALLY going to have to remember this!

• ###### 6. Re: Determine if string includes a letter

Dope.