I need to create the equivalent of four SQL CHECK constraints (one for each of four tables) based upon some combination of the concatenation of three numeric column values (batch no(4), company no(5), and employee no(5)). I can use whatever delimiters I want, but for now I'm using white space. So, here is a simple example of the four combinations.
"0 0 0" -- signals a master control row
"55 0 0" -- signals a batch control row
"555 888 0" -- signals a company control row
"5555 88888 0123" -- signals an employee calculation row
The following SQL REGEX validates for format.
(\d+)\s(\d+)\s(\d+)
But what I need is to be able to test the value of each group for appropriate equal-to zero or greater-than zero combinations.
(Explanation: Obviously, if I could use a compound test, i.e., with AND between tests, then I would be done. But I am using a third-party tool to redistribute and synchronize data between platforms and this tool only allows a single test (of what is normally a record-format indicator field) for determining which target table to which to distribute the source data--which is all in a single essentially flat file. But this particular file does not have a record-format indicator field. Instead, it has three fields and the applications check those field combinations for their own determinations.)
So, how to test a REGEX group's value for equal-to or greater-than zero?
ADDITIONAL: I did keep pursuing this myself and came up with these -- which I'm not sure are accurate. Will these do the job?
^([0]+)\s([0]+)\s([0]+)$
^([1-9][0-9]+)\s([0]+)\s([0]+)$
^([1-9][0-9]+)\s([1-9][0-9]+)\s([0]+)$
^([1-9][0-9]+)\s([1-9][0-9]+)\s([1-9][0-9]+)$
I will use these--and although the groups are not necessary, they make it clearer to me what I am doing.
^([0]+)\s([0]+)\s([0]+)$
^(0*[1-9]\d*)\s([0]+)\s([0]+)$
^(0*[1-9]\d*)\s(0*[1-9]\d*)\s([0]+)$
^(0*[1-9]\d*)\s(0*[1-9]\d*)\s(0*[1-9]\d*)$
Thank you, @sln