SQL Full Text Search Query?

Question

i am trying to implement a full text search on a login table and when i am executing the respective query then following are my observations:

When i am executing the following query implementing the full text search it is able to fetch approximate 1665 rows

select * from t_user where contains(LOGIN_ID, '"*david*" )

but when implementing the similar query without using full text search using the following it is returning 1872 rows

select * from T_USER where LOGIN_ID like '%david%'

As i browsed throw the data in my table i came to a conclusion that when i am using the contains keyword it is neglecting the row which has login id like DDAVID_D or i may say it is only returning rows which have the david words separated like DAVID_FRANK.

Is there a way to search a word between the words (like searching for David in between DDavidFrank or i may searching between long continous words for the david string like in DDAVIDFRANK ) as to implement full text search effectively ?

Answer 1

try

WHERE FREETEXT(LOGIN_ID, 'david') OR CONTAINS(LOGIN_ID, 'david')"

Answer 2

Try this query :

SELECT * from t_user WHERE CONTAINS(LOGIN_ID, '"david"' )