CODEWITHSUNDEEP
sqlsql-serverregexwildcard
Remzes
Remzes

Reputation: 269

Wildcard symbol % does not work as expected

I have a string in a column in SQL Server table:

30,36,40,41,42,52,62,63,66,67,76

As you see, this string has number 62 and number 66.

I am using this regex to match this string:

%62%76%

But it does not match this string.

How do I need to change this Regex to match the mentioned string?

The part of SQL code:

WHERE 
    (SELECT STUFF((SELECT ',' + CONVERT(NVARCHAR(MAX), TagID)
                   FROM ItemTag
                   WHERE ItemID = nt.ItemID
                   FOR XML PATH('')), 1, 1, '') AS tags) LIKE '%62%76%'

Very strange, but when I am using %30%52%, it match the string, but when I am using %30%62%, there is no match.

Upvotes: 0

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This seems like a crazy way to try to see if two values in are in the set. Instead:

where exists (select 1
              from ItemTags it
              where it.ItemId = nt.ItemId and
                    it.tagid = 62
             ) and
      exists (select 1
              from ItemTags it
              where it.ItemId = nt.ItemId and
                    it.tagid = 76
             );

Why would I use an adjective like "crazy" (which I do not actually mean in a literal, clinical sense)?

  • There is no need to convert an integer to a string for such a comparison.
  • There is no need to use functionality such as XML for such a comparison.
  • EXISTS should be quite efficient, particularly with an index on ItemTags(itemId, tagId).
  • LIKE simply should not be used for integer comparisons.

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46203

You need to specify ORDER BY TagID to ensure the TagID values are ordered as needed for the LIKE expression:

WHERE (SELECT STUFF(
  (
    SELECT ',' + CONVERT(NVARCHAR(MAX), TagID)
    FROM ItemTag
    WHERE ItemID = nt.ItemID
    ORDER BY TagID
    FOR XML PATH('')
  ), 1, 1, '') as tags
) LIKE '%62%76%'

Upvotes: 2

Related Questions