title: SQLite3 How to combine the IN operator with the LIKE operator to get partial text matching to a subquery?
body: <p>I am trying to do detect duplicates in a database. I have came up with a MWE of what I am trying to do. Not every id column has a value but the id nevertheless may be in the filename. I am trying to find all rows which have an id which appears in a filename which is in a different row.</p>
  
  <p><a href="https://i.stack.imgur.com/twzkn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/twzkn.png" alt="enter image description here"></a></p>
  
  <p>This query is kind of what I am looking for, but the problem is that doing exact matches</p>
  
  <pre><code>SELECT * FROM items WHERE id IN (
      SELECT filename FROM items
  );
  </code></pre>
  
  <p>The IN operator is shorthand for:</p>
  
  <p><code>name IN ("Bob Walters", "Alice Reed")</code> ==> <code>name == "Bob Walters" OR name = "Alice Reed"</code></p>
  
  <p>However I want an operator that does this:</p>
  
  <p><code>_________</code> ==> <code>"%" || name || "%" LIKE "Bob Walters" OR "%" || name || "%" LIKE "Alice Reed"</code></p>
  
  <p>I am looking for what I can put in the blank space to achieve this operation. My table has just over 10,000 rows (so the solution doesn't necessarily have to be optimized for millions of rows).</p>