How to allow only numbers and the - (hyphen) in a column

In Oracle, you can use CHECK constraints, REGEXP_LIKE, or TRANSLATE functions to allow only numbers and the - (hyphen) in a column. Here are different approaches:

---

1️⃣ Using CHECK Constraint

If you want to restrict a column to accept only numbers and hyphens, you can use a CHECK constraint with REGEXP_LIKE:

ALTER TABLE your_table ADD CONSTRAINT chk_valid_input 
CHECK (REGEXP_LIKE(your_column, '^-?\d+$'));

🔹 Explanation:

• ^-? → Starts with an optional -
• \d+ → Followed by one or more digits
• $ → Ensures the string ends after numbers

📌 Use Case: Prevents invalid data from being inserted.

---

2️⃣ Using REGEXP_LIKE in a Query

To filter out invalid rows in a query:

SELECT * FROM your_table 
WHERE REGEXP_LIKE(your_column, '^-?\d+$');

📌 Use Case: Selects only values with numbers and an optional leading hyphen.

---

3️⃣ Using TRANSLATE for Validation

If you need a function to validate the input dynamically:

SELECT your_column
FROM your_table
WHERE TRANSLATE(your_column, '0123456789-', ' ') IS NULL;

🔹 Explanation:

• TRANSLATE(your_column, '0123456789-', ' ') removes numbers and -, leaving only invalid characters.
• If the result is NULL, it means the column contains only numbers and -.

📌 Use Case: Works well for simple validations without using REGEXP_LIKE.

---

4️⃣ Using a BEFORE INSERT/UPDATE Trigger

If you need strict validation before inserting or updating:

CREATE OR REPLACE TRIGGER check_valid_input
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW
BEGIN
    IF NOT REGEXP_LIKE(:NEW.your_column, '^-?\d+$') THEN
        RAISE_APPLICATION_ERROR(-20001, 'Invalid input! Only numbers and hyphen allowed.');
    END IF;
END;
/

📌 Use Case: Prevents invalid inserts and updates.

####################################################################

If you want to allow numbers and hyphens anywhere in the value, you can modify the constraints accordingly. Here’s how:

5. CHECK Constraint

If you want to enforce this rule at the table level:

ALTER TABLE your_table ADD CONSTRAINT chk_valid_input 
CHECK (REGEXP_LIKE(your_column, '^[0-9-]+$'));

🔹 Explanation:

• ^[0-9-]+$ → Ensures only digits (0-9) and hyphens (-) are allowed, anywhere in the value.
• This allows values like:
  • 123-456
  • -98765
  • 56789
• But blocks invalid values like:
  • 12a34 ❌ (letters not allowed)
  • 12@34 ❌ (special characters not allowed)

---

6. Using REGEXP_LIKE in a Query

If you want to filter valid rows:

SELECT * FROM your_table 
WHERE REGEXP_LIKE(your_column, '^[0-9-]+$');

---

7. Using TRANSLATE for Validation

To check if a column contains only numbers and hyphens:

SELECT your_column
FROM your_table
WHERE TRANSLATE(your_column, '0123456789-', ' ') IS NULL;

🔹 How it works:

• Removes numbers and -, leaving invalid characters.
• If the result is NULL, the input is valid.

---

8. Using a BEFORE INSERT/UPDATE Trigger

To enforce this rule dynamically:

CREATE OR REPLACE TRIGGER check_valid_input
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW
BEGIN
    IF NOT REGEXP_LIKE(:NEW.your_column, '^[0-9-]+$') THEN
        RAISE_APPLICATION_ERROR(-20001, 'Invalid input! Only numbers and hyphens allowed.');
    END IF;
END;
/

📌 Use Case: Prevents invalid inserts and updates.

---

💡 Summary

Method	Use Case
CHECK Constraint	Restricts column values at the table level
REGEXP_LIKE in WHERE	Filters valid rows in a query
TRANSLATE Function	Simple validation without REGEXP
BEFORE INSERT/UPDATE Trigger	Prevents invalid values during insertion