title: Matching two column if they are identical/ similar, ignoring the special characters
body: <p>I have following table in postgres</p>
  
  <pre><code>
  col1        col2    col3                    col4
  
  NCT04132960 Drug    ds-8201a                ds 8201a
  NCT04132961 Drug    ds 8201a                ds 8201a
  NCT04132962 Drug    hydrogen sulphate       hydrogen sulphate
  NCT04132962 Drug    hydrogen sulphate       hydrogen
  NCT04132962 Drug    hydrogen sulphate       sulphate
  NCT04132963 Drug    bupropion (wellbutrin)  wellbutrin
  NCT04132964 Drug    bupropion (wellbutrin)  bupropion 
  NCT04132964 Drug    bupropion (wellbutrin)  bupropione 
  NCT04132964 Drug    bupropion (wellbutrin)  bup
  NCT04132965 Drug    Galantimine             Galantimine 
  NCT04132965 Drug    Galantimine             Galantimin
  NCT04132966 Drug    Glucose                     null
  </code></pre>
  
  <p>I am trying to clean above table based on following filters:</p>
  
  <pre><code>a. col3 should be similar/ identical to col4
  b. length of col4 &gt; 3
  c. include row if col4 is null
  </code></pre>
  
  <p>When I run below query I am able to match rows except row1 (which is missed due to special characters) and I am getting false positives. Below is the query and the output I am getting</p>
  
  <pre><code>SELECT *
  FROM table
  WHERE col3 ~* ('\y' || REGEXP_REPLACE(col4, '\+|\-', '\\+', 'g') || 'y') --replace regexp specific sign
  and length(col4) &gt; 3
  or col4 is null
  order by col1
  ;
  </code></pre>
  
  <p>How can I ignore the hits where col3 != col4 (eg. Galantimin, sulphate), but keeping them if they are same if I ignore the special characters (ds-8201a, ds 8201a) (bupropion (wellbutrin), wellbutrin),  and if the col4 is null.</p>
  
  <p>current output:</p>
  
  <pre><code>col1        col2    col3                    col4
  NCT04132961 Drug    ds 8201a                ds 8201a
  NCT04132962 Drug    hydrogen sulphate       hydrogen sulphate
  NCT04132962 Drug    hydrogen sulphate       hydrogen
  NCT04132962 Drug    hydrogen sulphate       sulphate
  NCT04132963 Drug    bupropion (wellbutrin)  wellbutrin
  NCT04132964 Drug    bupropion (wellbutrin)  bupropion 
  NCT04132964 Drug    bupropion (wellbutrin)  bupropione 
  NCT04132965 Drug    Galantimine             Galantimine 
  NCT04132965 Drug    Galantimine             Galantimin
  NCT04132966 Drug    Glucose                     null
  </code></pre>
  
  <p>desired output:</p>
  
  <pre><code>col1        col2    col3                    col4
  NCT04132960 Drug    ds-8201a                ds 8201a
  NCT04132961 Drug    ds 8201a                ds 8201a
  NCT04132962 Drug    hydrogen sulphate       hydrogen sulphate
  NCT04132963 Drug    bupropion (wellbutrin)  wellbutrin
  NCT04132964 Drug    bupropion (wellbutrin)  bupropion 
  NCT04132965 Drug    Galantimine             Galantimine 
  NCT04132966 Drug    Glucose                     null
  </code></pre>
  
  <p>Is it possible to handle this data cleaning in postgres or should I do it programmatically?</p>
  
  <p>Thanks</p>