Question 21886 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I create a REGEXP constraint on a column? – Mysql

I am trying to put a regexp constraint on a field "StaffAdress" such that it should only end with " street".

This is my attempt, but it still doesn’t seem to be valid. I tried regexp ‘.*street$’ and regexp ‘street$’ but both did not let me add "test street" in the table.

CREATE TABLE MsStaff (
   `StaffId` CHAR(4) PRIMARY KEY CHECK (`StaffId` regexp 'S[0-9][0-9][0-9]'),
    `StaffName` VARCHAR(100) NOT NULL,
    `StaffGender` VARCHAR(6) NOT NULL,
    `StaffAddress` VARCHAR(100) NOT NULL CHECK ('StaffAddress' regexp '.*street$')
)

I’m using 10.4.19-MariaDB

Tags:

2

Answers


  1. 0

    I think your problem is that you are comparing a literal string, not a column, to your regex.

    ...CHECK ('StaffAddress' regexp '.*street$')

    should be (don’t forget the space)

    ...CHECK (StaffAddress regexp '.* street$')
    Login or Signup to reply.
  2. 0

    'StaffAddress' regexp '.*street$' will always return false. Column names have to be quoted in backticks, not in single quotes.

    Correct would be:

    `StaffAddress` VARCHAR(100) NOT NULL CHECK (`StaffAddress` regexp 'w* street$')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search