Searching a table column with a specific word

Question

Let's say I have a table with two columns:

ID, FullName

And I have the following records in it:

John Bob Smith
John Bobby Smith

My problem is:

I want to return only the row that contains the matching word "Bob", and I don't want to return any rows that contain similar words like "Bobby"

When I search using :

Select * 
From Table1 
Where FullName like '%bob%'

I get the two rows which is wrong.

Select * 
From Table1 
Where FullName = 'Bob'

this return no rows at all.

Select * 
From Table1 
Where FullName like 'Bob'

this also does not return any rows.

I tried to use different wildcards but nothing works, I also tried the CHARINDEX and the PATINDEX but they do not return the needed results too.

Any suggestions?

Thanks.

Answer 1

SELECT * 
FROM Table1 
WHERE FullName LIKE '%Bob%'
  AND FullName NOT LIKE '%Bobby%'

Or perhaps you mean to avoid any Bobby and Bobban and Boby and Bobbie, etc...:

SELECT * 
FROM Table1 
WHERE FullName = 'Bob'             --- just Bob
   OR FullName LIKE 'Bob %'        --- starts with Bob
   OR FullName LIKE '% Bob'        --- ends with Bob
   OR FullName LIKE '% Bob %'      --- middle name Bob

This will miss however rows with 'John Bob-George Smith' and 'John Bob. Smith', etc. If you want such extended functionality, an SQL function that splits strings as proposed by Albin is a better approach.