Regex to match exact word without spaces

Question

I have a query such as the below:

SELECT * from table_name where lastname regexp "[[:<:]]Smith[[:>:]]"

This returns

• De Smith
• Smith

I only need to retrieve Smith

I even tried the below

SELECT * from surnames where last_name regexp "[[:<:]][^\s]Smith[[:>:]]"

Answer 1

I may be mistaking your requirement, but if you want to exactly match a last name then you can just use the equals operator:

SELECT * from table_name where TRIM(lastname) = 'Smith'

I used TRIM() on the lastname field just in case there might be leading or trailing whitespace.