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])+[%]")

2
  • 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? Jul 2, 2019 at 12:50
  • Updated the question Jul 2, 2019 at 13:05

3 Answers 3

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.

1
  • You know what they say about solving a problem with regex... :) Jul 2, 2019 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
3
  • 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. Jul 2, 2019 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. Jul 2, 2019 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. Jul 2, 2019 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
  • 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. Jul 2, 2019 at 18:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .