2

I am trying to write a validation rule which will allow only numbers, decimal and % symbol.

Below are the samples i am trying but it is not working. If I put 17.5% it is not working

  • Any number upto 100 (0-100)
  • Number with % symbol = 0% to 100%
  • Decimal = 0.00% to 100.00%

!REGEX(Text_Field__c , "[0-9]+(.[0-9])+[%]")

  • Can you please edit your question to give more detail about what's not working? What are some inputs that are being invalidated but should be valid, and vice-versa? – Thomas Taylor Jul 2 at 12:50
  • Updated the question – learningmode Jul 2 at 13:05
2

In your regex, you're looking for one or more digits (any number of them), followed by one or more instances of any single character (the period) and a digit, followed by a required percent sign. 17.5% should pass - I'm not sure why it's not. But so would 67534&8(9h6%. I think you should separate the max value part from the regex part. Try this:

!REGEX(Text_Field__c, "[0-9]{1,3}(\\.[0-9]{1,2})?%?") || 
    IF(RIGHT(Text_Field__c,1) = "%", 
        VALUE(LEFT(Text_Field__c,LEN(Text_Field__c)-1)) > 100, 
        VALUE(Text_Field__c) > 100)

The regex says to look for at least 1, but not more than 3 digits: [0-9]{1,3}. Then a group of characters consisting of a period (which has to be double-escaped with backslashes, once for Salesforce and again for the regex engine) and 1 or 2 instances of a digit: (\\.[0-9]{1,2}). The ? means that whole group should appear once or not at all. And then %? matches a percentage mark once, or not at all. Then the IF block checks to make sure that the number part is <= 100.

  • You know what they say about solving a problem with regex... :) – DavidSchach Jul 2 at 17:52
1

Salesforce Regex has the same syntax as Java except for escape \ which has to be transformed as \\. You can test your regex with this tool : https://www.freeformatter.com/java-regex-tester.html You can also test with Salesforce Apex and anonymous code your regular expression : https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_pattern_and_matcher_using.htm

Concerning your regex, there is several problems. First, to allow numbers without digits and percent symbol, we have to add a ? for the digit part and the % to indicate that is optional. We have also to escape the dot symbol. The regex becomes in Java : [0-9]+(\.[0-9]+)?[%]? In Salesforce : [0-9]+(\\.[0-9]+)?[%]?

Second, to restrict to numbers between 0 and 100, you can add to your validation rule :

|| VALUE( SUBSTITUTE(Field__c, '%', '')) < 0 || VALUE(SUBSTITUTE(Field__c, '%', '')) > 100

Complete rule :

NOT(REGEX(Field__c, '[0-9]+(\\.[0-9]+)?[%]?')) || VALUE(SUBSTITUTE(Field__c, '%', '')) > 100
  • 1
    Your regex will allow more than 2 decimal places, which @learningmode implies isn't wanted, but it's not totally clear. Your Java regex needs to escape the . to match a literal decimal point, and then Salesforce needs to escape the escape with an additional backslash, as well. Your use of SUBSTITUTE() is smart & better than my string manipulation, but I think the regex ensures that the number is >= 0, so you'd only need the <= 100 test. – Thomas Taylor Jul 2 at 14:30
  • The + only applies to the [0-9] class. The escape symbols have been deleted in my answer. I will correct. And you are right, testing that the number is >=0 is not necessary. – Mael Monnier Jul 2 at 14:37
  • right - but yours allows, eg 75.678%, mine would fail with more than 2 digits after the decimal. Not sure which learningmode is after. – Thomas Taylor Jul 2 at 14:46
1

Don't use Regex. Use VALUE() in your Validation Rule:

!ISNUMBER(VALUE(SUBSTITUTE(Field__c), "%",""))

Now you can add a line like

0 <= VALUE(SUBSTITUTE(Field__c), "%","") <= 100

And just put in the right logic or/and bits between them

  • 1
    I like your thinking! As written, this solution would allow entries like 6%.234 . You could add RIGHT(Text_Field__c,1) != "%" && !ISNUMBER(IF(RIGHT(Text_Field__c,1)) , but then the advantage over the regex diminishes. And if they want to limit the decimal places to 2, I think regex is better. – Thomas Taylor Jul 2 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.