title: Sybase - Creating a test in SQL comparing two columns with strings that have the same substrings but not the same positions
body: <p>The title might be a little hard to comprehend because I'm bad at summarizing things, but what I'm trying to do is actually very simple (in any other language).</p>
  
  <p>I want to test some data in a Sybase table, specifically two columns. These two columns have colon separated strings - and unfortunately, the position of each substring is not the same between both columns. It's something like this:</p>
  
  <p>column_1</p>
  
  <blockquote>
    <p>substring1:substring3:substring5</p>
    
    <p>substring2:substring4:substring6</p>
  </blockquote>
  
  <p>column_2</p>
  
  <blockquote>
    <p>substring3:substring1:substring5</p>
    
    <p>substring6:substring2:substring4</p>
  </blockquote>
  
  <p>What I want to do is provide a simple pass/fail status if substrings of column1 do not equal the substrings of column2. I don't care about the order of the substrings and can't change the order of them before they get to the table. I just want to be able to say:</p>
  
  <p>The test passes if column1 has the same content as column2 regardless of order, and if column1 or column2 contain information that is not present in the other, then it fails.</p>
  
  <p>I could do this blindfolded in something like Python, but I'm not very proficient in SQL. Here's roughly how I'm querying the table:</p>
  
  <pre><code>select columna, columnb, columnc, columnd, column_1, column_2, CASE &lt;do tests here&gt; END as test_results from table
  </code></pre>
  
  <p>Any thoughts? I want to keep it a simple as possible ideally!</p>
  
  <p>I now know that each substring will always be exactly 10 characters long, and always be colon separated. However, the number of substrings can vary from 1 - 20 (most will be 2 or 3).</p>
  
  <p>This is Sybase ASE, using RapidSQL 8.6.1, the version of the ADE driver I have installed is 15.07.00.1260 though I don't know much more about our DB setup than that.</p>