Databases
StackOverflow2013
Postsdbo
POCheck if `LIKE` patterns intersects in Postgres
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCheck if `LIKE` patterns intersects in Postgres
    primarykey
    data
    text
    <p>There ara two strings in some request that are patterns that used within <code>LIKE</code> expressions (with <code>_</code> and <code>%</code> placeholders). I want to find if this patterns intersects (have some string that matches them both). Is there any way to do that?...</p> <hr> <p>“Like pattern” corresponds to finit or infinit set of strings. Each string in this set matches to given pattern. I want to check if intersection of string sets for two given patterns is not empty. Thus it is better to say patterns conjunction. In a math language:</p> <blockquote> <p><em>S</em> — set of strings<br> <em>P</em> — set of patterns (where each pattern has one or more string representation)</p> <p><em>Sᵢ</em> — subset of strings (<em>Sᵢ ⊂ S</em>) that match pᵢ pattern (where instead of i could be any index). In equation form: “<em>Sᵢ = {s | s ∈ S, s matches pᵢ, pᵢ ∈ P}</em>” — that meas: “<em>Sᵢ</em> is a set of elements that are strings and match <em>pᵢ</em> pattern”. Or another notation: “<em>Sᵢ ⊂ S, ∀pᵢ ∈ P ∀s ∈ S (s matches pᵢ ≡ s ∈ Sᵢ)</em>” — that meas: “<em>Sᵢ</em> is subset of strings and any string is element of <em>Sᵢ</em> if it matches <em>pᵢ</em> pattern”.</p> <p>Let's define conjunction of patterns: “<em>p₁ ∧ p₂ = p₃ ≡ S₁ ∩ S₂ = S₃</em>” — that means: “Set of strings that match conjunction of patterns <em>p₁</em> and <em>p₂</em> is intersection of sets of strings that match <em>p₁</em> pattern and that match <em>p₂</em> pattern”.</p> </blockquote> <hr> <p>For example:</p> <ul> <li><code>ab_d</code> and <code>%cd</code> — intersects</li> <li><code>k%n</code> and <code>kl___</code> — intersects</li> </ul>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTimofey Gorshkov
    1. USTimofey Gorshkov
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCheck if `LIKE` patterns intersects in Postgres
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCheck if `LIKE` patterns intersects in Postgres
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCheck if `LIKE` patterns intersects in Postgres
      1. USDirk
      1. VTFavorite
    1. CODo you want to know if they intersect given the right string or if they actually intersect on a known set of strings?
      singulars
      1. POCheck if `LIKE` patterns intersects in Postgres
      1. USKlas Lindbäck
    2. COTo test a string against multiple patterns you can use `LIKE ALL`, eg: `SELECT 'comm' LIKE ALL (ARRAY['_omm', 'co%']);` . Figuring out whether any two patterns have one or more strings that both patterns match, though... that's hard. One way might be to create candidate strings from one pattern and match them to the other, but it'd be rather slow and imperfect. I suspect you won't solve this easily in SQL.
      singulars
      1. POCheck if `LIKE` patterns intersects in Postgres
      1. USCraig Ringer
    3. COIn my answer (which I've now deleted) Errandir made the comment *"But there couldn't be `'%mm'` string that intersects `'_omm'` and `'co%'` too instead of `'comm'`?"*. I don't understand what you are asking there. Are you looking to find patterns that match *exactly one* common string, where there are not two or more strings that match both? Please edit your question to explain your problem better and add more examples of patterns that do and do not match according to the rules you want to implement - and why.
      singulars
      1. POCheck if `LIKE` patterns intersects in Postgres
      1. USCraig Ringer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload