How do we express repeating numbers in oracle?

Question

I have a field with an id value.

a ) The value of this field cannot be values ​​like 0, 00000000, 99999999. How do I express this in oracle? Will I do it with Regex?

b ) The field cannot be all letters and cannot contain characters such as periods or commas. I did the option a as follows but is it true?

select x_ID from table1 where regexp_like(x_ID,'[a-zA-Z]')

edit : It is not desired to consist of the same number value, for example 77777777. It cannot be shorter than 3 characters in length, for example 0, 12, 11. However, it can have a value like 12345678. AbCdEf or ABCDEF is wrong. Because it is not wanted to consist only of letters. It would be true if it was AbCdEf1 or ABCDEF1 because it also contains a numerical value. I will state all of these conditions as a separate condition in the query. 1- not null 2- The length of the x_ID field cannot be less than 3. and two more conditions I wrote above.

Answer 1

The value of this field cannot be values ​​like 0, 00000000, 99999999. How do I express this in oracle? Will I do it with Regex?

You can use the regular expression ^(.)\1*$ to detect strings that consist solely of one-or-more repetitions of a single character:

SELECT *
FROM   table_name
WHERE  REGEXP_LIKE( id, '^(.)\1*$' );

---

If you want to add conditions to a column then you can add constraints for each one. Some examples could be:

ALTER TABLE table_name ADD CONSTRAINT id_longer_than_2
  CHECK ( LENGTH( id ) > 2 );
ALTER TABLE table_name ADD CONSTRAINT id_must_contain_lower_case
  CHECK ( REGEXP_COUNT( id, '[[:lower:]]' ) > 0 );
ALTER TABLE table_name ADD CONSTRAINT id_must_contain_upper_case
  CHECK ( REGEXP_COUNT( id, '[[:upper:]]' ) > 0 );
ALTER TABLE table_name ADD CONSTRAINT id_must_contain_digit
  CHECK ( REGEXP_COUNT( id, '[[:digit:]]' ) > 0 );
ALTER TABLE table_name ADD CONSTRAINT id_must_not_repeat
  CHECK ( NOT REGEXP_LIKE( id, '^(.)\1+$' ) );
ALTER TABLE table_name ADD CONSTRAINT id_is_only_alphanumeric
  CHECK ( REGEXP_LIKE( id, '^[[:alnum:]]+$' ) );