Look for text in one field contained in another field Oracle SQL

Question

I apologize if this question has been asked, I'm having trouble putting it into words.

I've been asked to filter out rows in a query where text from one field is contained in another field. An example would probably explain it better:

    Column_1         Column_2
    Low Static       Static
    Static           Static
    Static           Clear
                     Static
    Very Low Freq    Freq

The result of the query should return only rows 3 and 4, since rows 1, 2, and 5 contain strings that are similar. Right now, I have the following condition:

    WHERE
    ((Column_2 NOT LIKE '%' || Column_1 || '%')
    OR (Column_1 NOT LIKE '%' || Column_2 || '%' OR Column_1 IS NULL))

However, it's returning rows 1, 3, 4, and 5 when I want to only return rows 3 and 4. This is just example data, my actual dataset contains many different text strings in columns 1 and 2, so I can't just write specific case statements to exlcude certain instances where the columns are similar.

Maybe this just isn't possible, since I'm unable to define a string as something contained within 2 spaces, while at the same time taking into consideration cases where there are no spaces?

Thanks

Answer 1

For your expression, I think you want and rather than or:

WHERE ((Column_2 NOT LIKE '%' || Column_1 || '%') AND
       (Column_1 NOT LIKE '%' || Column_2 || '%' OR Column_1 IS NULL)
      )

You need for both conditions to be true. You might find the logic easier to follow as:

WHERE NOT (Column_2 LIKE '%' || Column_1 || '%' OR
           Column_1 LIKE '%' || Column_2 || '%'
          )