Regex to fail if multiple matches found

Question

Take the following regex:

P[0-9]{6}(\s|\.|,)

This is designed to check for a 6 digit number preceded by a "P" within a string - works fine for the most part.

Problem is, we need the to fail if more than one match is found - is that possible?

i.e. make Text 4 in the following screenshot fail but still keep all the others failing / passing as shown:

(this RegEx is being executed in a SQL .net CLR)

Answer 1

If the regex engine used by this tool is indeed the .NET engine, then you can use

^(?:(?!P[0-9]{6}[\s.,]).)*P[0-9]{6}[\s.,](?:(?!P[0-9]{6}[\s.,]).)*$

If it's the native SQL engine, then you can't do it with a single regex match because those engines don't support lookaround assertions.

Explanation:

^                         # Start of string
(?:                       # Start of group which matches...
 (?!P[0-9]{6}[\s.,])      # unless it's the start of Pnnnnnn...
 .                        # any character
)*                        # any number of times
P[0-9]{6}[\s.,]           # Now match Pnnnnnn exactly once
(?:(?!P[0-9]{6}[\s.,]).)* # Match anything but Pnnnnnn
$                         # until the end of the string

Test it live on regex101.com.

Answer 2

or use this pattern

^(?!(.*P[0-9]{6}[\s.,]){2})(.*P[0-9]{6}[\s.,].*)$

Demo
basically check if the pattern exists and not repeated twice.

^               Start of string
(?!             Negative Look-Ahead
  (             Capturing Group \1
    .           Any character except line break
    *           (zero or more)(greedy)
    P           "P"
    [0-9]           Character Class [0-9]
    {6}         (repeated {6} times)
    [\s.,]          Character Class [\s.,]
  )             End of Capturing Group \1
  {2}           (repeated {2} times)
)               End of Negative Look-Ahead
(               Capturing Group \2
  .             Any character except line break
  *             (zero or more)(greedy)
  P             "P"
  [0-9]         Character Class [0-9]
  {6}           (repeated {6} times)
  [\s.,]            Character Class [\s.,]
  .             Any character except line break
  *             (zero or more)(greedy)
)               End of Capturing Group \2
$               End of string