CODEWITHSUNDEEP
sql-serversql-server-2008
Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

Return rows containing all words in a string

I want to retrieve all rows in which a certain column contains all words passed in a string parameter regardless of the order in which they appear.

If the parameter is 'hi abc' then I want this row: abc def hijk and not hijk lmnop qr

I managed to do it but I suspect it is not very good so I would like to see alternatives. How to better accomplish what my code below do?

create table t (s varchar(200));
insert into t (s) values
('abc def hijk'),
('hijk lmnop qr'),
('stu'),
('v xyz')
;

create function dbo.matchRows (@string varchar(max))
returns varchar(max)
begin

    set @string = replace(@string, '''', '''''');
    set @string = replace(@string, '  ', ' ');
    set @string = replace(@string, '  ', ' ');
    set @string = replace(@string, ' ', '%'' and s like ''%');
    set @string = 's like ''%' + @string + '%''';
    set @string = 'select * from t where ' + @string;
    return @string;

end;

declare @query varchar(max);
set @query = (select dbo.matchRows('hi abc'));
execute (@query);

Upvotes: 0

Views: 412

Answers (1)

CompuChip
CompuChip

Reputation: 63

Something like this should work. It converts your search params into XML and then splits it into a table variable. After that, it searches your @t table for all of the split up parameters that you passed in (where the count of the found words equaling the number of search parameters makes it match all of them).

DECLARE @SearchStringParams varchar(max),
  @Split char(1),
  @Xml xml,
  @NumOfSearchTerms Int
DECLARE @SplitTable table (valToSearchFor varchar(100));

SELECT @SearchStringParams = 'hi,abc',
@Split = ','

SELECT @Xml = CONVERT(xml,'<root><s>' + REPLACE(@SearchStringParams,@Split,'</s><s>') + '</s></root>')

INSERT @SplitTable
SELECT [Value] = T.c.value('.','varchar(20)')
FROM @Xml.nodes('/root/s') T(c)

SELECT @NumOfSearchTerms = @@ROWCOUNT

DECLARE @t table (searchWords varchar(200));
insert into @t (searchWords) values
('abc def hijk'),
('hijk lmnop qr'),
('stu'),
('v xyz')
;
select t.searchWords
from @t t inner join @SplitTable s
on t.searchWords like ('%' + s.valToSearchFor + '%')
group by t.searchWords
having count(t.searchWords) = @NumOfSearchTerms

Upvotes: 4

Related Questions