title: oracle sql select with employeees
body: <p>i have one interesting question, and could not resolve it. Please help!!!!</p>
  
  <p>They are  tables</p>
  
  <pre><code>t_employee
     ID             NUMBER,
    DEPARTMENT_ID  NUMBER,
    CHIEF_ID       NUMBER,
    NAME           VARCHAR2(100 BYTE),
    SALARY         NUMBER,
    BIRTH_DATE     DATE,
    ADDRESS        VARCHAR2(200 BYTE),
    STATUS         VARCHAR2(1 BYTE)
  </code></pre>
  
  <p>and </p>
  
  <pre><code>t_department
    ID    NUMBER,
    NAME  VARCHAR2(100 BYTE)
  </code></pre>
  
  <p>Need display the number of employees in each region - in Adress column (if they are now region then = 'No' area).
  Names of areas converted to uppercase.</p>
  
  <p>What is problem? Problem is that adress collumn has unstructured data for example:
  Adress:</p>
  
  <pre><code>Country,REGION,city,... 
  </code></pre>
  
  <p>So REGION always must be beetween first (,) and second (,) AND must include word (reg) 
  For example:</p>
  
  <pre><code>Russia(Country), reg Moskovskay ,  Moscow(city), Lenina, (street)  .... or 
  Russia(Country), Moskovskay reg ,  Moscow(city), Lenina, (street)  .... or
  </code></pre>
  
  <p>separator is (,)
  position is - second</p>
  
  <p>Many thanks!</p>