Full-text email column search terminates when using wildcard characters

Question

I am trying to do a full-text search in a table containing email addresses.

Say my table contains an email address: [e-mail]

Now because of word breaks, the periods in this email address function as a separator. The default SQL Server column prevents individual characters from being indexed (for obvious reasons).

This is usually not a problem and the address search works fine. However, I want to be able to search for parts of the address.

I would like to find " abbuilder @ real " using the following query. This will not work because the address is not indexed as " abbuilder @ real ... ".

SELECT * FROM [Address Book] WHERE CONTAINS ([a]. *, '" Abbuilder @ real *"')

Any suggestions on how to solve this? SQL Fiddle Test Example .

Answer 1

You can use the LIKE operator:

SELECT * FROM [Addressbook] a WHERE EmailAddress LIKE 'a.b.builder@real%';

CONTAINS. , .

, wildcard, :

SELECT * FROM [Addressbook] a WHERE CONTAINS([a].*, 'a.b.builder@real*')

, . , , - - LIKE, , CONTAINS. , LIKE.

SQL Fiddle