Multiple conditional statement checking SQL

Question

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.

Can you outline all other scenarios and edge cases? Will @pattern always have 2 patterns, or can it be 1, 2, 3, or more? Will the patterns always be separated by a space, and no space could ever be inside a pattern? Will the patterns always be enclosed in leading/trailing wildcards? — Aaron Bertrand
– Aaron Bertrand, Commented Feb 15, 2017 at 4:56
I need to support more than 2 patterns. They are separated by a single space. I can remove % from patterns. '%|35=[A,D]|% %|34=[1]|%' could be '|35=[A,D]| |34=[1]|' — Sam Mokari
– Sam Mokari, Commented Feb 15, 2017 at 5:30
(Also, I would stay away from defining column aliases in 'single quotes' - this is deprecated, and good riddance, because they make it very cumbersome to tell string constants from column aliases. When necessary, use [square brackets] or - if you like causing readers pain - "double quotes". — Aaron Bertrand
– Aaron Bertrand, Commented Feb 15, 2017 at 5:40

Aaron Bertrand · Accepted Answer · 2017-02-15 05:36:49Z

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
...

Just one question, what can I do if I want to find tag34 with value 100,120 ? — Sam Mokari
– Sam Mokari, Commented Feb 17, 2017 at 2:40

Stack Exchange Network

Multiple conditional statement checking SQL

1 Answer 1

Your Answer

Hot Network Questions

Multiple conditional statement checking SQL

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions