CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Samira
Samira

Reputation: 21

How to implement not like with like in SQL query

I have table UserRule with this sample data:

Ruleid   IsActive    Title      Description    Content     RuleName    priority
 -------------------------------------------------------------------------------        
 100      1        %Test%       %One%          %Reza%      Contain       1
 101      1        Talebi       Ahmad          Zahra       Equal         2
 102      1        %Ali         %Omid          %Kaveh      StartWith     3
 103      1        Samira%      Mina%          Amir%       EndWith       4

For Contain, Equal, StartWith and EndWith operation, this stored procedure works:

Alter procedure GetRule
    @Title nvarchar(max),
    @Description nvarchar(max),
    @Content nvarchar(max)
as 
begin
     Select top(1) 
         UserRule.*  
     from 
         UserRule 
     where 
         IsActive = 1 
         and @Title like UserRule.Title
         and @Description like UserRule.Description
         and @Content Like UserRule.Content
     Order by 
         UserRule.Priority  ASC

Now, we want to implement negative state for RuleName column for example (not contain, not equal, not startwith and not endwith) with the same stored procedure. How to implement not like with like?

Upvotes: 2

Views: 1228

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239774

You cannot, in general. Except for %, every other part of a LIKE pattern is an assertion about a single character. So, _ matches any single character. [...] matches any single character that matches those characters expressed within the []. [^...] matches any single character that isn't matched by the characters expressed within the [].

So, an expression like NOT LIKE 'abc' matches any string that isn't exactly three characters long or any three character string that isn't exactly abc. There's no way to express even something as simple as this with a LIKE pattern. E.g. you may think that LIKE '[^a][^b][^c]' is the equivalent, but some quick thinking will demonstrate otherwise. It won't accept strings that aren't exactly 3 characters long. And it will reject strings such as dbe just because it does contain a b in the second position.

You will need to re-write your query to explicitly handle negative matches, rather than expecting to be able to write "equivalent" LIKE patterns.

E.g. assuming an additional column in your table, called NegateMatch, being a bit with 1 meaning to negate the match, you'd have something like:

where 
IsActive=1 
and
(
  (
    NegateMatch = 0
    and @Title like UserRule.Title
    and @Description like UserRule.Description
    and @Content Like UserRule.Content
  )
  or
  (
    NegateMatch = 1
    and not
    (
      @Title like UserRule.Title
      and @Description like UserRule.Description
      and @Content Like UserRule.Content
    )
  )
)
Order by UserRule.Priority  ASC

Upvotes: 1

Sagar R
Sagar R

Reputation: 613

In sql server it will work something like this,This is just to show how not like can be use

SELECT * FROM GetRule WHERE @Title not LIKE '%' +REPLACE ( UserRule.Title, '^' , '' ) + '%'

Upvotes: 0

Nagahornbill
Nagahornbill

Reputation: 121

As per my understanding of your question, I will suggest to add one more parameter say, @IfContains bit = 0 in the stored proc and implement the stored proc as below ... 

Alter procedure GetRule
@Title nvarchar(max),
@IfContains   bit = 0

As
BEGIN

If IfContains = 0 Then
BEGIN
    Select  top(1) UserRule.*  from UserRule where 
    IsActive=1 and  @Title like UserRule.Title
    Order by UserRule.Priority  ASC
END

If IfContains = 1 Then
BEGIN
    Select  top(1) UserRule.*  from UserRule where 
    IsActive=1 and  @Title Not like UserRule.Title
    Order by UserRule.Priority  ASC
END

END

In this way it won't break the existing calls to the stored proc in your application and you can pass the second parameter if you want to use the "Not Like" in where clause of your query.

Upvotes: 0

Related Questions