How to use Microsoft SQL CONTAINS to search for rows where the column contains a word at any position, similar to LIKE '%word%'?

Question

My Customer table has a full-text index on column Name. (I checked that the column is full-text index by using this answer and by right clicking on the column -> properties -> Full Text = true)

The names are usually first name and surname, e.g.

Name
---------------
Joe
Joe Doe
Joe Bar
Foo Joe
Alice Bob
Charles David
Elizabeth Fred

When I search for all customers where the name contains the word "Doe" using the CONTAINS predicate

SELECT * FROM mst_Customers WHERE CONTAINS(Name,'Doe')

nothing is returned

When I search for all customers where the name contains "Joe"

SELECT * FROM mst_Customers WHERE CONTAINS(Name,'Joe')

the first 3 records are turned (instead of all 4 customers where the name contains the word Joe)

Name
---------------
Joe
Joe Doe
Joe Bar

How do I use CONTAINS to return all customers that somewhere in the name contain "Doe"?

The equivalent using LIKE

SELECT * FROM mst_Customers WHERE Name LIKE '%Doe%'

gives me the desired results.

Answer 1

This works perfectly... Demo :

CREATE TABLE T_NOM
(NOM_ID INT IDENTITY CONSTRAINT PK_NOM PRIMARY KEY, 
 NOM_NOM VARCHAR(32))

INSERT INTO T_NOM VALUES 
('Joe'), 
('Joe Doe'), 
('Joe Bar'), 
('Foo Joe'), 
('Alice Bob'), 
('Charles David'), 
('Elizabeth Fred');
GO

CREATE FULLTEXT CATALOG FT;
GO

CREATE FULLTEXT INDEX ON T_NOM (NOM_NOM) KEY INDEX PK_NOM ON FT;
GO

SELECT * FROM T_NOM WHERE CONTAINS(NOM_NOM,'Doe');