CODEWITHSUNDEEP
stringsql-server-2012sql-like
Chevelho51
Chevelho51

Reputation: 25

SQL Server: use all the words of a string as separate LIKE parameters (and all words should match)

I have a string containing a certain number of words (it may vary from 1 to many) and I need to find the records of a table which contains ALL those words in any order.

For instances, suppose that my input string is 'yellow blue red' and I have a table with the following records:

1 yellow brown white
2 red blue yellow
3 black blue red

The query should return the record 2.

I know that the basic approach should be something similar to this:

select * from mytable where colors like '%yellow%' and colors like '%blue%' and colors like '%red%'

However I am not being able to figure out how turn the words of the string into separate like parameters.

I have this code that splits the words of the string into a table, but now I am stuck:

DECLARE @mystring varchar(max) = 'yellow blue red';
DECLARE @terms TABLE (term varchar(max));
INSERT INTO @terms
SELECT Split.a.value('.', 'NVARCHAR(MAX)') term FROM (SELECT CAST('<X>'+REPLACE(@mystring, ' ', '</X><X>')+'</X>' AS XML) AS String) AS A CROSS APPLY String.nodes('/X') AS Split(a)
SELECT * FROM @terms

Any idea?

Upvotes: 0

Views: 322

Answers (2)

Charlieface
Charlieface

Reputation: 72415

If you are storing a delimited list in a single column then you really need to normalize it out into a separate table.

But assuming you actually want to just do multiple free-form LIKE comparisons, you can do them against a list of values:

select *
from mytable t
where not exists (select 1
    from (values
        ('%yellow%'),
        ('%blue%'),
        ('%red%')
    ) v(search)
    where t.colors not like v.search
);

Ideally you should pass these values through as a Table Valued Parameter, then you just put that into your query

select *
from mytable t
where not exists (select 1
    from @tmp v
    where t.colors not like v.search
);

If you want to simulate an OR semantic rather than AND the change not exists to exists and not like to like.

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

First, put that XML junk in a function:

CREATE FUNCTION dbo.SplitThem
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN ( SELECT Item = y.i.value(N'(./text())[1]', N'nvarchar(4000)')
      FROM ( SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i));

Now you can extract the words in the table, join them to the words in the input string, and discard any that don't have the same count:

DECLARE @mystring varchar(max) = 'red yellow blue';

;WITH src AS 
(
  SELECT t.id, t.colors, fc = f.c, tc = COUNT(t.id)
  FROM dbo.mytable AS t
  CROSS APPLY dbo.SplitThem(t.colors, ' ') AS s
  INNER JOIN (SELECT Item, c = COUNT(*) OVER()
    FROM dbo.SplitThem(@mystring, ' ')) AS f
  ON s.Item = f.Item
  GROUP BY t.id, t.colors, f.c
)
SELECT * FROM src
WHERE fc = tc;

Output:

id colors fc tc
2 red blue yellow 3 3

This disregards any possibility of duplicates on either side and ignores the larger overarching issue that this is the least optimal way possible to store sets of things. You have a relational database, use it! Surely you don't think the tags on this question are stored somewhere as the literal string

string sql-server-2012 sql-like

Of course not, these question:tag relationships are stored in a, well, relational table. Splitting strings is for the birds and those with all kinds of CPU and time to spare.

Upvotes: 1

Related Questions