How can you use a string variable that has SQL inside a SQL LIKE wildcard pattern expression?-CodePudding

I have a SELECT statement with a WHERE clause that uses a LIKE wildcard with an expression coming from a variable string that I have built using values from a table. However, although there are no errors, the query is not evaluating correctly to give the expected results, as it is returning all the results records and not applying the condition in the LIKE wildcard. The desired result should be fewer records returned as they will be filtered against the LIKE wildcard expression.

The variable I have built to use in the SQL query's LIKE wildcard is the following, along with the value of the string:

Declare @TrimmedAgeType Varchar(450);

The value is this: '%J%' AND PERM.AgeFilter NOT LIKE '%JS%'

The SQL query looks like this:

SELECT * FROM #MembershipInfo #PSP
INNER JOIN TRP_ME_PriceStructure PERM 
    ON #PSP.MembershipPriceStructurePK = PERM.MembershipPriceStructurePK
    WHERE PERM.AgeFilter NOT LIKE   @TrimmedAgeType

When I hardcode the query with the string value below, I get the expected results returned back.

SELECT * FROM #MembershipInfo #PSP
INNER JOIN TRP_ME_PriceStructure PERM 
    ON #PSP.MembershipPriceStructurePK = PERM.MembershipPriceStructurePK
    WHERE PERM.AgeFilter NOT LIKE   '%J%' AND PERM.AgeFilter NOT LIKE '%JS%'

If you want to know how I am initially building the string variable value, see below:

Declare @AgeType Varchar(450);
Declare @AgeReference varchar(450)
SET @AgeType = CONCAT(@AgeType, '''%'   @AgeReference   '%''')   ' AND PERM.AgeFilter NOT LIKE '

Additional Info:

This @AgeType variable is being generated inside a while loop that is looping through a table of Age References. I later change the name of the variable to @TrimmedAgeType as I do some additional work on it to remove the last occurrence of ' AND PERM.AgeFilter NOT LIKE '

More Info: I have a table of memberships with the AgeFilter column containing letter references ('A,U,J,C,JS', etc). I want to return all results that do not match the given references in the LIKE wildcard pattern. This is because the SELECT query is actually going to be a DELETE query and I will delete all records that are returned and do not match the given references in the LIKE wildcard pattern.

Hope someone can help. Thank you.

CodePudding user response：

Let's say we have some filters stored here:

CREATE TABLE dbo.FilterPatterns
(
  PatternID int PRIMARY KEY,
  FilterPattern varchar(32) NOT NULL UNIQUE
);

To simulate the question, let's say we know the PatternID values we're after are 1 and 3:

INSERT dbo.FilterPatterns(PatternID, FilterPattern)
  VALUES(1,'%J%'),(2,'%Q%'),(3,'%JS%');

We've got some members stored in a #temp table for reasons unknown (how is that populated?):

CREATE TABLE #MembershipInfo
(
  MembershipPriceStructurePK int PRIMARY KEY
);

Let's insert a few rows to match and not match (note: 30 doesn't exist in the core table).

INSERT #MemberShipInfo VALUES(5),(10),(15),(20),(30);

Two of these will have matching age filters in the core table, the other two will not. We'll also add a row not in the temp table (25):

CREATE TABLE dbo.TRP_ME_PriceStructure
(
  MembershipPriceStructurePK int PRIMARY KEY,
  AgeFilter varchar(256)
);

INSERT dbo.TRP_ME_PriceStructure VALUES
(5,'foo'),(10,'BobJoined'),(15,'blat'),(20,'NodeJS'),(25,'funky');

Now our query can just do a NOT EXISTS against the filter patterns table using whatever logic you're currently using to pull values from that table to build the string:

SELECT * FROM #MembershipInfo #PSP
INNER JOIN dbo.TRP_ME_PriceStructure PERM 
    ON #PSP.MembershipPriceStructurePK = PERM.MembershipPriceStructurePK
    WHERE NOT EXISTS
    (SELECT 1 FROM 
    dbo.FilterPatterns AS fp
    WHERE fp.PatternID IN (1,3)
    AND PERM.AgeFilter LIKE fp.FilterPattern);

members 5 and 15 are returned
members 10 and 20 are left out because they matched the filter
members 25 and 30 are left out because they miss the join

Example db<>fiddle

CodePudding user response：

Try to hard code the wildcard

SELECT * FROM #MembershipInfo #PSP
INNER JOIN TRP_ME_PriceStructure PERM 
    ON #PSP.MembershipPriceStructurePK = 
PERM.MembershipPriceStructurePK
    WHERE PERM.AgeFilter NOT LIKE   '%'  @TrimmedAgeType   '%'

CodePudding user response：

Using Dynamic SQL I have successfully written the query, returning me the expected results:

DECLARE @DeleteUnsuitableMemberships varchar(MAX)
SET @DeleteUnsuitableMemberships =
'DELETE #MembershipInfo FROM #MembershipInfo #PSP
INNER JOIN TRP_ME_PriceStructure PERM 
    ON #PSP.MembershipPriceStructurePK = PERM.MembershipPriceStructurePK
    WHERE PERM.AgeFilter NOT LIKE '   @TrimmedAgeType

EXEC(@DeleteUnsuitableMemberships)

I did was the following:

Declare a new variable.
Set that variable to the SQL query which I have wrapped in apostrophe (').
Appended the variable using the operator.
Used the EXEC() function with the variable placed inside as a parameter.

The value of @TrimmedAgeType is still '%J%' AND PERM.AgeFilter NOT LIKE '%JS%'

Thank you all for helping.