CODEWITHSUNDEEP
sql-serverstored-proceduresfull-text-search
SlowCoder74
SlowCoder74

Reputation: 93

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

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?

Upvotes: 0

Views: 2103

Answers (3)

SlowCoder74
SlowCoder74

Reputation: 93

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.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82020

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

Upvotes: 1

AVK
AVK

Reputation: 3923

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

Upvotes: 1

Related Questions