title: How to split column data into rows in Informatica
body: <p>To split column data into rows in Informatica:</p>
  
  <p>Sample data:</p>
  
  <pre><code>ID     VALUE
  123    lod,!,cfgda,!,mwtbvmailg,!
  456    fod,!,cbgda,!
  789    cfgda,!,mwtbv,!mailg,!cxsew,!
  </code></pre>
  
  <p>SQL query to split this data:</p>
  
  <pre><code> SELECT DISTINCT ID, VALUE FROM 
    (SELECT DISTINCT ID, 
     UPPER (TRIM (REGEXP_SUBSTR (SUBSTR (VALUE, 1), '[^,!]+', 1,
     LEVELS.COLUMN_VALUE))) AS VALUE FROM T1,
   TABLE (CAST (MULTISET (SELECT LEVEL FROM DUAL 
   CONNECT BY LEVEL &lt;= LENGTH (REGEXP_REPLACE (SUBSTR (VALUE, 1), '[^,!]+')) 
   + 1) AS SYS.ODCINUMBERLIST)) LEVELS)
   WHERE VALUE IS NOT NULL;
  </code></pre>
  
  <p>Here, if we try to use Normalizer in Informatica ETL, the code doesn't work fine as normalizer works only for fixed number of rows to be split and in our case, the data is dynamic.</p>
  
  <p>So, using Informatica Java Transformation, we were able to achieve desired output:</p>
  
  <p>In EXP:</p>
  
  <pre><code>i/p port = VALUE
  variable port = REPLACECHR(0, VALUE, ',!', ' ')
  o/p port = variable port
  </code></pre>
  
  <p>In Java transformation, paste below text:</p>
  
  <pre><code>// To split VALUE to multiple rows
  String str=VALUE;
  String[] temp;
  String delimiter =" ";
  temp = str.split(delimiter);
  for (int i =0; i&lt; temp.length; i++)
  {
  VALUE = temp[i];
  generateRow();
  }
  </code></pre>
  
  <p>Column VALUE then splits into multiple rows as below:</p>
  
  <pre><code>ID     VALUE
  123    lod
  123    cfgda
  123    mwtbvmailg
  456    fod
  456    cbdga
  etc.....
  </code></pre>
  
  <p>I got it working this way, any other suggestions?</p>