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:


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 Feb 15 '17 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 Feb 15 '17 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 Feb 15 '17 at 5:40

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.

   @List       varchar(max),
   @Delimiter  varchar(255)
       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

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 Feb 17 '17 at 2:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.