SQL LIKE query rule

I have a requirement where I can search for a house number and needs to match one way but not another

If i search for 40

it should return a match for 40, 40c 40 biz

however, it should not return a match for 400, 4020 etc

Would this require some form of regex type like query? I am struggling to think how I can do this in SQL

Answer

Hmmm . . . If I understand correctly, you could do:

where col + ' ' like '40[^0-9]%'