Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Replace in string Whole Word confusion

  1. #1
    Join Date
    Jun 2017
    Posts
    18

    Default Replace in string Whole Word confusion

    Okay so I have some values coming in for numeric fields. Sometimes I get just a plain period and sometimes I get actual numerical values such as 12.34

    I want to replace the periods when it is only a period in that field, because this is being loaded into redshift into a numeric column and trying to insert a period makes it fail

    When I tried a string replace to do periods to 0's, it did the following:

    . --> 0
    12.34 --> 12034

    Then I tried the option for Whole Word set to Y because I though it would use the logic "only replace if the entire field value matches the search field". However it gave me this:

    . --> .
    12.34 --> 0


    Am I doing something wrong or is there an option to only replace when the entire value is a thing. I don't want to do a massive string of filters and replaces...

  2. #2
    Join Date
    May 2016
    Posts
    282

    Default

    The REPLACE IN STRING step won't work for that, as you have experienced, it is for replacing characters in a string, you can't make it replace the whole string if it matches your pattern. I would use the NULL IF step (if it works Null instead of 0, although you could combine it with the IF NULL step) or the REGEX EVALUATION or FORMULA steps.
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  3. #3
    Join Date
    Nov 2009
    Posts
    688

    Default

    With the REGEX EVALUATION you can check with this regex: ^\d*[.]?\d*$
    Last edited by johanhammink; 03-09-2018 at 05:59 AM.

  4. #4
    Join Date
    Aug 2016
    Posts
    290

    Default

    Sounds like a pretty simple java expression:

    fieldName.equals(".") ? "0" : fieldName

    Easier to understand than regex.

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.