Querying records that start/end with a string, within word boundaries using REGEXP (MySql)

Question

In the below query, I'd like to find records that start with engineer . e.g. I'd like to pull back records with the description engineering

SELECT * FROM app.desc_test t
WHERE lower(t.desc) REGEXP '[[:<:]]engineer[[:>:]]';

The word boundaries are properly handling all special characters (i.e. commas, spaces, special characters, etc that are before and after), but I'm not sure how to write the Regex so that it starts with engineer.

Also, how would I make this say starts with OR ends with engineer.

• Somewhat similar issue, but in .NET
• Similar issue, but looking for double quotes in MySQL
• MySQL 5.7 regex docs

CREATE TABLE desc_test (
  id int(11) NOT NULL AUTO_INCREMENT,
  desc varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Edit
The value will be unknown/dynamic, so hardcoding any "ing" expression isn't the solution.

Answer 1

If you only want to match the beginning of the word, you can just remove [[:>:]] from the regexp.

SELECT * FROM app.desc_test t
WHERE lower(t.desc) REGEXP '[[:<:]]engineer';