Remove SUB Key

yichuansancun

• January 21, 2015 00:00

• Answered

Follow

I got a data dump from an Oracle system where there are a lot of

SUB

keys. See reference here regarding SUBSTITUTE (ctl-Z). Is there a way to remove these keys in the process of transformation? These keys are non-blank and non-null, so they throw off the transformation statements such as

ifnull 

or

 if isblank

.

• Screen Shot 2015-01-20 at 10.26.12 PM.png

• 

  slechtaj

  • January 21, 2015 12:55

  0

  Comment actions Permalink

  Hi,
  
  you may simply use replace() CTL function. Just like as follows:
  

  
  $out.0.myString = replace($in.0.myString, "\u001A","");;
  

  
  Where the first argument is the string where you want to search for characters from the second argument. All matching expressions in the string will be replaced with the third argument. You should keep in mind that it is regular expression replace function, therefore you should be aware of special chars used in Java regular expressions.
  
  Hope this helps.
• 

  yichuansancun

  • February 09, 2015 17:20

  0

  Comment actions Permalink

  Hi I tried with the following code but getting error:
  
  

  
  $out.0.* = replace($in.0.*,"\u001A","");
  

  
  
  Error:
  

  
  Function replace(string,string,string) is not applicable for the arguments (record(Input_Address),string,string)
  

  
  
  How can I convert my record to be string?
  
  Thanks,
  Perri
• 

  dpavlis

  • February 10, 2015 13:26

  0

  Comment actions Permalink

  You can not use the replace function on the whole record - $in.0.* - as there may potentially be some non-string fields.
  
  You need to apply it on individual string fields - see the first example.

Please sign in to leave a comment.