title: How to find multiple spaces inside text for a varchar2 field in Oracle 11g?
body: <p>I don't recall ever seeing a field like this before, but it combines the city, state, and zipcode into a single string of varchar2.  Fortunately, I believe most of the fields are in the same city space state, space zipcode format, but I started finding a few that deviated from that norm.  </p>
  
  <p>Right now I'm trying to identify all these distinct conditions
  in the database with over 5 million rows and my queries aren't working for what I wanted.   </p>
  
  <p>I started with:</p>
  
  <pre><code>SELECT PROJECT_CTY_ST_ZIP FROM PAYMENT WHERE PROJECT_CTY_ST_ZIP LIKE '%' || CHR(32) || '%';
  </code></pre>
  
  <p>Then tried:</p>
  
  <pre><code>SELECT PROJECT_CTY_ST_ZIP FROM PAYMENT WHERE PROJECT_CTY_ST_ZIP LIKE '% %' AND PROJECT_CTY_ST_ZIP LIKE '% %'  AND PROJECT_CTY_ST_ZIP LIKE '% %';
  </code></pre>
  
  <p>but they are both pulling based on leading and trailing spaces and I was really wanted to find spaces in the inside of the text.  I don't want to remove them, just identify them with a query so I can parse them properly in my java code and then do an insert later to put them into city, state, and zipcode fields in another table.</p>
  
  <p>While it doesn't show it here, I found this field in IA with no leading spaces, then one leading space and then two leading spaces.  I fixed the leading spaces with trim.</p>
  
  <p>WEST LIBERTY, IA 52776  </p>
  
  <p>This last one I wasn't expecting and I wanted to see if there are other conditions that might be unusual, but my query doesn't find them as the spaces are in the middle of the text:</p>
  
  <p><strong>TRUTH OR CONSEQUENCE, NM 87901</strong> </p>
  
  <p>How would I go about a query to find these kinds of distinct records?</p>