How to split column data into rows in Informatica
<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>