Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Remove first character and replace others

  1. #1
    Join Date
    Jan 2016
    Posts
    8

    Question Remove first character and replace others

    Hi,

    I need to be able to remove the first character in a string field if it is a hash (#), and then replace any other occurrences, but I'm not sure how to do it (I'm not really a techie). The string has values such as '#http://xxxx.xxxx#http://xxxx.xxx'

    So if the first character is a hash (it may not be) I want to remove it and then replace the second hash with a semi-colon (/ (not a smelly face!) as a delimiter. so the above would look like 'http://xxxx.xxxx;http://xxxx.xxx'.

    Any help on what component to use and how would be really appreciated.

    Many thanks

    Andy
    Last edited by Donoss; 03-10-2017 at 12:03 PM.

  2. #2
    Join Date
    Nov 2013
    Posts
    382

    Default

    Most people here would say use Java ... I would do it with a simple Formula step

    if (left([MyField];1)="#" ;
    substitute(mid([MyField];2;len([MyField])-1);"#";";") ;
    [MyField])

    If your string can have a third # , you can specify to change only one #
    substitute(mid([MyField];2;len([MyField])-1);"#";";";1) ;
    Last edited by DepButi; 03-10-2017 at 12:26 PM. Reason: Add missing ) and substitute to ; (not : as I wrote initially)

  3. #3
    Join Date
    Jan 2016
    Posts
    8

    Red face

    Quote Originally Posted by DepButi View Post
    Most people here would say use Java ... I would do it with a simple Formula step

    if (left([MyField];1)="#" ;
    substitute(mid([MyField];2;len([MyField])-1);"#";";") ;
    [MyField])

    If your string can have a third # , you can specify to change only one #
    substitute(mid([MyField];2;len([MyField])-1);"#";";";1) ;
    This works perfect, many thanks ....

  4. #4
    Join Date
    Dec 2016
    Posts
    7

    Default Using User Defined Java Expression

    We can also use 'User Defined Java Expression' step to do this. Here is my sample ktr file.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.