SQL Server: contains' vs' charindex '

Question

I have a problem with a query in ms sql server. I have a full text index in column "col1". The data in this column can become quite large (20, 30 kb +). Now I want to search for the exact phrase in this column.

I was told that the “contains” function is the fastest function for this, but I know at least 2 other ways to do this; using a "similar" function and using "charindex".

The problem is that "contains" does not work when I search for a phrase that contains the # character. For example, "... WHERE contains (col1, '" query string # "') ..." will always return 0 results.

I switched to using charindex and this returns the results, but it takes a lot more time for this task to query the database.

Is there a way to speed up this request or get the contains function to accept my # character?

Thank you for your time...

Update I decided to switch between using the charindex contains function. Therefore, if the request data contains the # character, we switch to using charindex; for all other queries I use contains. It seems to work better.

Answer 1

FTS. , Microsoft .

, , -. , : "zxzHASHyxy".

"" .

, . , .

.

Answer 2

, "#", . # xyz xyz.

FREETEXT:

FREETEXT: , CONTAINS. SQL Server . ,   CONTAINS.

Answer 3

- LIKE/ CHARINDEX()? , LIKE , CHARINDEX(), , .

:

• - # ?
• If so, you could use a two-step case in which you use SQL CONTAINS () to retrieve a list of all the records containing the query string (with or without #) and then test on the application side to delete records that don't have #?

Answer 4

ALTER FULLTEXT INDEX ON  [dbo].[Tablename]  SET STOPLIST = OFF

This helps to use a special character inside.

Example:

 select * from  [dbo].[Tablename] where contains(Column_Name,'C# or C++')