Regex fails if multiple matches are found

Question

Take the following regular expression:

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

This is intended to verify that the 6-digit number preceded by a “P” inside a string works mostly for the most part.

The problem is that we need to fail if more than one match is found - is this possible?

i.e. make text 4 in the following screenshot invalid, but still leave all other dips / traversals as shown:

(this RegEx runs in SQL.net CLR)

Answer 1

If the regex engine used by this tool is really a .NET engine, you can use

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

SQL-, , .

:

^                         # Start of string
(?:                       # Start of group which matches...
 (?!P[0-9]{6}[\s.,])      # unless it 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

regex101.com.

Answer 2

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

-
, .

^               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