Search code examples
informatica-powercenter

Informatica column splitting

I have a source which has only one column as shown below 

Col1
123@abcdef$68#ghij
trsp#728&hmbb6378
$@#1267rtxc

I want to load this source to below target table with three columns . col1 should hold only alphabets , col2 should hold only numeric , col3 should hold only special characters 

col1  col2  col3

Please tell me the approach in Informatica .

Solution

  • Use REPLACECHR like in this link https://forgetcode.com/informatica/1816-replacechr-replace-characters-in-a-string-with-some-other-character-or-remove-them

    First replace all characters a to z and 1 to 9 with blank to get a variable port lets call portB with only the special characters. Then use the portB as the replacecharacters in a second variable lets call portC based on the original input port. Create your numbers port lets call portD by replacing all characters of portC with blank. Create your alphabets port lets call portE by replacing all the numbers of portC with blank. Create one output port portF to be equal to portB. Connect portD, portE and portF to your target