CODEWITHSUNDEEP
sql-servert-sql
CB_Ron
CB_Ron

Reputation: 599

How can I match all the rows from one table to a column in another table in t-sql?

I don't know if the method I have in mind (which the question relates to) is the best way to arrive at the desired result, so if anyone has a better idea I'm all ears. Here's what I'm trying to accomplish. Given a space-delimited set of search terms, I want to search a particular column, with consistent results regardless of the order of the terms. So e.g. "brown dog" and "dog brown" should return similar results. I have a table-valued function that takes a string and a delimiter and returns the split string as a single-column table. 

CREATE FUNCTION dbo.Split
(
    @char_array varchar(500), @delimiter char(1)
)
RETURNS 
@parsed_array table
(
    Parsed varchar(50)
)
AS
BEGIN
    DECLARE @parsed varchar(50), @pos int

    SET @char_array = LTRIM(RTRIM(@char_array))+ @delimiter
    SET @pos = CHARINDEX(@delimiter, @char_array, 1)

    IF REPLACE(@char_array, @delimiter, '') <> ''
    BEGIN
        WHILE @pos > 0
        BEGIN
            SET @parsed = LTRIM(RTRIM(LEFT(@char_array, @pos - 1)))
            IF @parsed <> ''
            BEGIN
                INSERT INTO @parsed_array (Parsed) 
                VALUES (@parsed)
            END
            SET @char_array = RIGHT(@char_array, LEN(@char_array) - @pos)
            SET @pos = CHARINDEX(@delimiter, @char_array, 1)

        END
    END 
    RETURN
END
GO

The column I want to search is a varchar(2000). Right now I'm searching it like so:

SELECT ID FROM Table WHERE Description LIKE '%' + @searchTerm + '%'

But that doesn't return consistent results given the scenario I described above where multiple terms are in a different order. Is it possible to somehow join the table from the function to the column and get the desired result? Note the results should contain all rows where the Description contains all search terms in any order.

Upvotes: 0

Views: 84

Answers (1)

Rhys Jones
Rhys Jones

Reputation: 5508

To answer your question just from a SQL perspective, Yes, you can join the function to the table (I've gone via a temp table though) and use a HAVING clause to make sure all search terms are included, something like this.

SELECT
    f.Parsed
INTO 
    #s
FROM
    dbo.Split(@terms) f

DECLARE @c INT = (SELECT COUNT(*) FROM #s)

SELECT 
    t.ID 
FROM 
    Table t 
        INNER JOIN #s s on t.Description LIKE '%' + s.Parsed + '%'
GROUP BY
    t.ID
HAVING 
    COUNT(*) = @c

However, as comments to the question allude, this approach to searching leaves a lot of open questions. If you can take a look at SQL Server Full-Text Search - more effort to setup but much better results.

Upvotes: 1

Related Questions