Trying to Create REGEXP Pattern That Will Detect Numbers Separated by a Space

[theof]

Hi all. I'm trying to create a REGEXP pattern that will check if a string contains a set of numbers separated by a space. I have tried the 4 statements below. They all return 0. What am I doing wrong? Thanks.

SELECT '0630 3062' REGEXP '^[0-9]{4} [[:space:]] [0-9]{4}$';

SELECT 'Employee 346 retrad: 0630 3062' REGEXP '[0-9]{4} [[:space:]] [0-9]{4}';

SELECT 'Employee 346 retrad: 0630 3062' REGEXP '[0-9]+ [[:space:]] [0-9]+';

SELECT 'Employee 346 retrad: 0630 3062 1657 in July Assessment' REGEXP '[0-9]+ [[:space:]] [0-9]+';

The statements are also in this db fiddle

Thanks.

[June7]

I tested with a VBA function. These all work:
"[0-9]{4} [0-9]{4}"
"\d{4} \d{4}"
"\d{4}\s\d{4}"

However, they all match on data with groups of 4 or more numbers: "abc 12345 78987"). Using {4,4} made no difference. Could be a deficiency in VBA RegExp which also doesn't like [[:space:]].

Fiddle queries work by just removing spaces from before and after [[:space:]], however, only the first one is correct if number groups have more than 4 digits. This also works: '^[0-9]{4} [0-9]{4}$'

None of the versions used in VBA worked in the queries.

[johnsonmillie53]

The issue with the regular expressions you provided is that they are looking for a specific format of the string where the numbers are 4 digits and are separated by exactly one space.

In the first statement, the regular expression '^[0-9]{4} [[:space:]] [0-9]{4}$' is looking for a string that starts with 4 digits, followed by exactly one space, then again followed by 4 digits and the string should end there. So it's not matching any of the strings.

In the second and third statement, the regular expression '[0-9]{4} [[:space:]] [0-9]{4}' and '[0-9]+ [[:space:]] [0-9]+' is looking for a string that contains 4 digits, followed by exactly one space, then again followed by 4 digits.

In the fourth statement, the regular expression '[0-9]+ [[:space:]] [0-9]+' is looking for a string that contains one or more digits, followed by exactly one space, then again followed by one or more digits.

In all cases, the regular expressions are looking for a specific format of the string and not considering the rest of the string.

Here's a regular expression that can help you match any number separated by one or more whitespaces in a string:

\b\d+\b\s+\b\d+\b

This pattern uses the word boundaries (\b) to ensure that the digits matched are not part of a larger number or word and the \s+ will match one or more whitespace.

You can also use this pattern to match any number separated by any non-digit characters

\b\d+\b[^\d]+\b\d+\b

This pattern will match number(s) separated by one or more non-digit characters.

You can test these patterns in your SELECT statement like this:

SELECT 'Employee 346 retrad: 0630 3062' REGEXP '\b\d+\b\s+\b\d+\b'

Please keep in mind that the specific regular expression pattern that you use may depend on the specific requirements of your application. The examples provided are just a starting point and may need to be adjusted or expanded based on your specific use case.