Search 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 entries:

 John Bob Smith John Bobby Smith 

My problem:

I want to return only a string containing the corresponding word "Bob", and I do not want to return strings containing similar words like "Bobby"

When I do a search using:

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

I get two lines that are wrong.

 Select * From Table1 Where FullName = 'Bob' 

this does not return any rows.

 Select * From Table1 Where FullName like 'Bob' 

it also does not return any rows.

I tried using different wildcards, but nothing works, I also tried CHARINDEX and PATINDEX , but they also do not return the desired results.

Any suggestions?

Thanks.

Answer 1

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

Or maybe you want 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 

In this case, there will be no lines with 'John Bob-George Smith' and 'John Bob. Smith' 'John Bob. Smith' etc. If you want to use such advanced functionality, it is best to use the SQL function, which separates the rows proposed by Albin.

Answer 2

There are no built-in functions in SQL Server for comparing whole words.

If you are running SQL Server, you can create a CLR UDF to create a regular expression method or create a regular SQL function to split the strings .