title: Teradata SQL: select rows based on values in two columns where order of name and surname is not important in Teradata SQL?
body: <p>I have in Teradata SQL table like below:</p>
  <pre><code>col1             | col2
  ----------------------------
  Adam Nowak PHU   | Nowak Adam
  AAR Kowal Jan    | Kowal Jan
  Tomasz Gut       | Juk Anna
  </code></pre>
  <p>And I would like to select only these rows where:</p>
  <ul>
  <li>in col1 I have value from col2 + something (nevermind before or after value)</li>
  <li>Be aware that order of name and surname is not important so Jan Kowal is the same that Kowal Jan</li>
  </ul>
  <p>So as a result I need something like below, so only first and second rows because there are in col1 value from col2 + something else and order of name and surname is not important:</p>
  <p><a href="https://i.stack.imgur.com/j7rfQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/j7rfQ.png" alt="enter image description here" /></a></p>
  <p>I used query like below, but my query does not take into account that order of name and surname is not important and for my code Jan Kowal and Kowal Jan are different persons.</p>
  <pre><code>where upper(col1) like '%' || upper(col2) || '%'
  </code></pre>
  <p>Moreover I used code which was perfect to my case and work correctly but only on MS SQL Server, on Teradata SQL id does not work:</p>
  <pre><code>WHERE
    upper(col1) LIKE '%' + substring(upper(col2), 1, CHARINDEX(' ',col2)-1) + '%'
  AND
    upper(col1) LIKE '%' + substring(upper(col2), CHARINDEX(' ',col2)+1, LEN(col2)) + '%'
  </code></pre>
  <p>How can I do taht in Teradata SQL? Could you modify my code which work on MS SQL Server or suggest your own solution which work on Teradata SQL ?</p>