Sql-server – Multiple conditional statement checking SQL

sql serversql server 2014t-sql

I have a table with different columns. One of them is Message. I want to check multiple patterns using like in this case.

For a start position here is a simple patterns:

DECLARE @pattern VARCHAR(50) = '%|35=[A,D]|%';

For matching this pattern, I use this query:

SELECT Logid 'LogId', 'Message' Message  
FROM Messages 
WHERE LogId >= 1000 AND
    (m.Message like @pattern);

And it is an example of Message content:

|8=FIX.4.4|9=70|35=A|34=1|10=008|

Now I want to check a complex pattern, which is:

DECLARE @pattern VARCHAR(50) = '%|35=[A,D]|% %|34=[1]|%';

It means that we need to have a message with %|35=[A,D]|% AND %|34=[1]|%. I need to match with a complex pattern with n parts which there is a space between conditions. The previous example is a condition with to statement.

So, first of all, I need to create a list of conditions by splitting pattern and check all of the conditions and make a simple AND between results. I would be glad if I can do that. The last point is that the pattern is an input of a stored procedure.

Best Answer

Assuming no pattern can contain a space, the following may work. First, you need a splitting function that can handle a space (also a problem for some splitting techniques), and that returns the number of rows after the split.

CREATE OR ALTER FUNCTION dbo.SplitStrings
(
   @List       varchar(max),
   @Delimiter  varchar(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN ( WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 
       FROM n WHERE n <= LEN(@List))
     SELECT [Value] = SUBSTRING(@List, n, 
       CHARINDEX(@Delimiter, @List + @Delimiter, n) - n), 
       Fragments = COUNT(*) OVER() FROM n 
     WHERE n <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, n, 1) = @Delimiter
   );
GO

Now, you can pass your pattern into that function, and you get returned the set of patterns. We need to check that all patterns are found in a given string, which means the number of rows that match in the join must equal the number of patterns. This translates to something like this:

DECLARE @BaseTable TABLE(SearchColumn varchar(255));

INSERT @BaseTable(SearchColumn) VALUES
  ('|8=FIX.4.4|9=70|35=A|34=1|10=008|'),  -- will match
  ('|8=FIX.4.4|9=70|35=QA|34=1|10=008|'), -- will not match
  ('|8=FIX.4.4|9=70|35=A|34=71|10=008|'); -- will not match

DECLARE @pattern varchar(50) = '%|35=[A,D]|% %|34=[1]|%';

SELECT bt.SearchColumn 
  FROM @BaseTable AS bt
  INNER JOIN dbo.SplitStrings(@pattern, ' ') AS ss
  ON bt.SearchColumn LIKE ss.[Value]
  GROUP BY bt.SearchColumn
  HAVING COUNT(bt.SearchColumn) = MAX(ss.Fragments);

If a pattern may contain spaces, you should simply use a different delimiter between your patterns, e.g. three tildes (~~~):

DECLARE @pattern varchar(50) = '%|35= [A, D]|%~~~%|34 = [1]|%';

...
  INNER JOIN dbo.SplitStrings(@pattern, '~~~') AS ss
...
Related Question