Results 1 to 3 of 3

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

Hybrid View

  1. #1
    Join Date
    Sep 2022
    Posts
    1

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

    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.


    Code:
    SELECT '0630 3062' REGEXP '^[0-9]{4} [[:space:]] [0-9]{4}$';
    Code:
    SELECT 'Employee 346 retrad: 0630 3062' REGEXP '[0-9]{4} [[:space:]] [0-9]{4}';
    Code:
    SELECT 'Employee 346 retrad: 0630 3062' REGEXP '[0-9]+ [[:space:]] [0-9]+';
    Code:
    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.

  2. #2
    Join Date
    Nov 2020
    Posts
    26
    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.
    Last edited by June7; 10-01-2022 at 02:50 PM.

  3. #3
    Join Date
    Aug 2022
    Posts
    20
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •