Oracle regex match pattern ending with ABBA-CodePudding

I want to be able to find strings that end with a pattern "ABBA", where A - digit B - digit, not equal to A.

Consider a string - 88889889 which matches the above pattern. Generally, I would use negative lookahead in other languages like below

But Oracle does not support negative lookahead. How can this be achieved in Oracle? Oracle version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

I have tried implementing something like this:

select 1 "val"
 from dual
where regexp_like('88889889','\S (\d)(\d)\2\1$');

However, it matches both 88889889 and 88889999.

CodePudding user response：

If it doesn't have to be a regular expression, fairly simple conditional substr does the job:

Sample data:

SQL> with test (col) as
  2    (select 88889889 from dual union all  -- valid
  3     select 12345432 from dual union all  -- invalid
  4     select 443223   from dual union all  -- valid
  5     select 1221     from dual            -- valid
  6    ),

Just for simplicity, extract the last 4 digits (you'd, of course, discard all values that are shorter than 4 digits, right?):

  7  temp as
  8    (select col, substr(col, -4) last4
  9     from test
 10    )

Finally:

 11  select col,
 12    case when substr(last4, 1, 1) = substr(last4, 4, 1) and
 13              substr(last4, 2, 1) = substr(last4, 3, 1) then 'valid'
 14         else 'invalid'
 15    end result
 16  from temp;

       COL RESULT
---------- -------
  88889889 valid
  12345432 invalid
    443223 valid
      1221 valid

SQL>

CodePudding user response：

Option 1:

You can compare the capturing group values to ensure that they are not equal:

SELECT value,
       1 "val"
FROM   table_name
WHERE  REGEXP_SUBSTR(value,'\S (\d)(\d)\2\1$', 1, 1, NULL, 1)
       <> REGEXP_SUBSTR(value,'\S (\d)(\d)\2\1$', 1, 1, NULL, 2);

Option 2:

Or, with simple (faster) string functions:

SELECT value,
       1 "val"
FROM   table_name
WHERE  SUBSTR(value, -4, 1) = SUBSTR(value, -1, 1)
AND    SUBSTR(value, -3, 1) = SUBSTR(value, -2, 1)
AND    SUBSTR(value, -4, 1) <> SUBSTR(value, -3, 1);

Option 3:

Or, if you have Java enabled in the database then you can create a Java function to match regular expressions:

CREATE AND COMPILE JAVA SOURCE NAMED RegexParser AS
import java.util.regex.Pattern;

public class RegexpMatch {
  public static int match(
    final String value,
    final String regex
  ){
    final Pattern pattern = Pattern.compile(regex);

    return pattern.matcher(value).matches() ? 1 : 0;
  }
}
/

Then wrap it in an SQL function:

CREATE FUNCTION regexp_java_match(value IN VARCHAR2, regex IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'RegexpMatch.match( java.lang.String, java.lang.String ) return int';
/

Then use it in SQL:

SELECT value,
       1 "val"
FROM   table_name
WHERE  REGEXP_JAVA_MATCH(value,'\S (\d)(?!\1)(\d)\2\1$') = 1;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT '88889889' FROM DUAL UNION ALL
SELECT '88889999' FROM DUAL UNION ALL
SELECT '88881234' FROM DUAL;

All three options output:

VALUE val

88889889 1

VALUE	val
88889889	1

db<>fiddle here