MS SQL - CONTAINS full-text search w/ variable number of values, not using dynamic sql

Question

I've created a full-text indexed column on a table.

I have a stored procedure to which I may pass the value of a variable "search this text". I want to search for "search", "this" and "text" within the full-text column. The number of words to search would be variable.

I could use something like

WHERE column LIKE '%search%' OR column LIST '%this%' OR column LIKE '%text%'

But that would require me to use dynamic SQL, which I'm trying to avoid.

How can I use my full-text search to find each of the words, presumably using CONTAINS, and without converting the whole stored procedure to dynamic SQL?

You might read this article about search type queries. sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries — Sean Lange
– Sean Lange, Commented Jul 25, 2016 at 15:07
@AaronBertrand - Our senior wants us to avoid as much as possible. — SlowCoder74
– SlowCoder74, Commented Jul 25, 2016 at 15:35
Right, but is there a good reason? In my experience dynamic SQL is by far the best approach to resolving queries with a variable number of where clauses. I call these "the kitchen sink." — Aaron Bertrand
– Aaron Bertrand, Commented Jul 25, 2016 at 16:10
@AaronBertrand I'm still learning SQL. It may be that he's trying to teach me "best practices" for the time-being. I'm actually learning a lot of ways to accomplish certain SQL tasks that I thought could only be done through dynamic SQL a couple months ago. — SlowCoder74
– SlowCoder74, Commented Jul 25, 2016 at 17:28

AVK · Accepted Answer · 2016-07-25 16:09:41Z

1

If you say you definitely have SQL Table Full Text Search Enabled, Then you can use query like below.

select * from table where contains(columnname,'"text1" or "text2" or "text3"' )

See link below for details

Full-Text Indexing Workbench

answered Jul 25, 2016 at 16:09

AVK

3,9231 gold badge24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Cappelletti · Accepted Answer · 2016-07-25 16:10:33Z

For your consideration.

I understand your Senior wants to avoid dynamic SQL, but it is my firm belief that Dynamic SQL is NOT evil.

In the example below, you can see that with a few parameters (or even defaults), and a 3 lines of code, you can:

1) Dynamically search any source 
2) Return desired or all elements
3) Rank the Hit rate

The SQL

Declare @SearchFor  varchar(max) ='Daily,Production,default'  -- any comma delim string
Declare @SearchFrom varchar(150) ='OD'                        -- table or even a join statment
Declare @SearchExpr varchar(150) ='[OD-Title]+[OD-Class]'     -- Any field or even expression
Declare @ReturnCols varchar(150) ='[OD-Nr],[OD-Title]'        -- Any field(s) even with alias 

Set @SearchFor = 'Sign(CharIndex('''+Replace(Replace(Replace(@SearchFor,' , ',','),', ',''),',',''','+@SearchExpr+'))+Sign(CharIndex(''')+''','+@SearchExpr+'))'
Declare @SQL varchar(Max) = 'Select * from (Select Distinct'+@ReturnCols+',Hits='+@SearchFor+' From '+@SearchFrom + ') A Where Hits>0 Order by Hits Desc' 
Exec(@SQL)

Returns

OD-Nr   OD-Title                    Hits
3       Daily Production Summary    2
6       Default Settings            1

I should add that my search string is comma delimited, but you can change to space.

Another note CharIndex can be substanitally faster that LIKE. Take a peek at http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex

SlowCoder74 · Accepted Answer · 2016-07-25 17:38:42Z

So I think I came up with a solution. I created the following scalar function:

CREATE FUNCTION [dbo].[fn_Util_CONTAINS_SearchString]
(
    @searchString NVARCHAR(MAX),
    @delimiter NVARCHAR(1) = ' ',
    @ANDOR NVARCHAR(3) = 'AND'
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    IF @searchString IS NULL OR LTRIM(RTRIM(@searchString)) = '' RETURN NULL

    -- trim leading/trailing spaces
    SET @searchString = LTRIM(RTRIM(@searchString))

    -- remove double spaces (prevents empty search terms)
    WHILE CHARINDEX('  ', @searchString) > 0
    BEGIN
        SET @searchString = REPLACE(@searchString,'  ',' ')
    END

    -- reformat
    SET @searchString = REPLACE(@searchString,' ','" ' + @ANDOR + ' "') -- replace spaces with " AND " (quote) AND (quote)
    SET @searchString = ' "' + @searchString + '" ' -- surround string with quotes

    RETURN @searchString
END

I can get my results:

DECLARE @ftName NVARCHAR (1024) = dbo.fn_Util_CONTAINS_SearchString('value1 value2',default,default)
SELECT * FROM Table WHERE CONTAINS(name,@ftName)

I would appreciate any comments/suggestions.

Collectives™ on Stack Overflow

MS SQL - CONTAINS full-text search w/ variable number of values, not using dynamic sql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related