CODEWITHSUNDEEP
sqlfull-text-search
Bob1254
Bob1254

Reputation: 75

SQL Full Text Search Problem

What am I doing wrong with my full text search? If this code 

Select UserID, UserName
From Users
Where Contains([UserName], '%Jack%')

I get the user jack back, but if I do this

Select UserID, UserName
From Users
Where Contains([UserName], '%Ja%')

I get nothing what am I doing wrong?

Upvotes: 0

Views: 169

Answers (2)

Chris M
Chris M

Reputation: 85

Contains([UserName], '"Ja*"') - Syntax for PREFIX search. Would match "Jack" but NOT "Ajax"

You cannot do any POSTFIX search with full text search. If you were to try:

Contains([UserName], '"*Ja*"') -- wrapped in *

This would actually still do a PREFIX ONLY search, it will strip out all special characters that are not proper syntax, meaning the first * would be stripped then ran. If you need open ended search you need to use %% still to find inner parts or words.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

You're mixing LIKE syntax with CONTAINS. Keep in mind that full text searching is word based, while like searches for a character pattern within a string.

Try:

Select UserID, UserName
From Users
Where Contains([UserName], '"Ja*"')

Upvotes: 3

Related Questions