CODEWITHSUNDEEP
sqlsql-servert-sql
Strangloss
Strangloss

Reputation: 5

How to do a SQL partial search using CONTAINS

I'm new to SQL and I'm trying to perform a partial search using CONTAINS which uses a variable. It works fine when looking for a "complete" term but not partial. Let's say there's a row with the entry "Tristan" nothing pops up when I'm using just "Trist". I tried to do this: @Word+'*', but it's not working. Below the code of my stored procedure.

ALTER PROCEDURE [dbo].[spSearchWord]
@Word nvarchar(100)

AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM [testDatabase].[dbo].[User]
WHERE CONTAINS (*, @Word)

END

Upvotes: 0

Views: 890

Answers (1)

user7396598
user7396598

Reputation: 1289

Important note for others:

CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types

To directly answer the question I found this:

Use of contains() in sql server

In combination with ZLK's comment:

@variable = @variable + '*';

SELECT *
FROM user
WHERE CONTAINS(column, @variable);

This * syntax is similar to LIKE ('value%')

It only works from the beginning of a word with wild card on the end. You cannot prefix with a wildcard, it is the nature of FULL-TEXT Indexing...wild cards cannot be indexed.

I found some interesting workarounds for the prefix during a search, but none that I would recommend. You would get better performance from doing a LIKE('%value%') search than any of the workarounds I came across.

Upvotes: 1

Related Questions