Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (value) AS
SELECT '11-1111111' FROM DUAL UNION ALL
SELECT '222222222' FROM DUAL UNION ALL
SELECT '123456789' FROM DUAL UNION ALL
SELECT '12-3456789' FROM DUAL UNION ALL
SELECT '1-23456789' FROM DUAL UNION ALL
SELECT '123-456789' FROM DUAL UNION ALL
SELECT '12345678-9' FROM DUAL UNION ALL
SELECT '12-3456-789' FROM DUAL UNION ALL
SELECT '12-3456-78' FROM DUAL;
9 rows affected

SELECT *
FROM   table_name
WHERE  -- Check that the value as the correct length
       LENGTH(value) IN (9, 10)
       -- Check that the value has the correct length without hyphens
AND    LENGTH(REPLACE(value, '-')) = 9
       -- Check that the value has only digits or hyphens
AND    TRANSLATE(value, 'a-0123456789', 'a') IS NULL
       -- Check that all the characters are not either hyphens or the same as the
       -- first character
AND    TRANSLATE(value, 'a-' || SUBSTR(value, 1, 1), 'a') IS NOT NULL;


VALUE


123456789

12-3456789

1-23456789

123-456789

12345678-9


SELECT *
FROM   table_name
WHERE  -- Check that the value has the correct format
       ( value LIKE '_________' OR value LIKE '__-_______' )
       -- Check that the other characters are digits
AND    TRANSLATE(
          SUBSTR(value, 1, 2) || SUBSTR(value, -7),
          'a0123456789',
          'a'
        ) IS NULL
       -- Check that all the characters are not either hyphens or the same as the
       -- first character
AND    TRANSLATE(value, 'a-' || SUBSTR(value, 1, 1), 'a') IS NOT NULL;


VALUE


123456789

12-3456789


SELECT *
FROM   table_name
WHERE  REGEXP_LIKE(value, '^\d{2}-?\d{7}$')
AND    NOT REGEXP_LIKE(value, '^(\d)\1-?\1{7}$');


VALUE


123456789

12-3456789


SELECT *
FROM   table_name
WHERE  REGEXP_LIKE(value, '^\d+-?\d+$')
AND    REGEXP_LIKE(value, '^[0-9-]{9,10}$')
AND    NOT REGEXP_LIKE(value, '^(\d)(-?\1){8}$');


VALUE


123456789

12-3456789

1-23456789

123-456789

12345678-9


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;
  }
}
/
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';
/
SELECT *
FROM   table_name
WHERE  REGEXP_JAVA_MATCH(value, '^(?!(\d)(-?\1){8}$)(?=(\d{9}|[0-9-]{10})$)\d+-?\d+$') = 1;


VALUE


123456789

12-3456789

1-23456789

123-456789

12345678-9