Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: How to integrate CHARINDEX and SUBSTRINg from SQL Server onto a Field in PDI

  1. #1
    Join Date
    Oct 2011
    Posts
    17

    Default How to integrate CHARINDEX and SUBSTRINg from SQL Server onto a Field in PDI

    All

    I'd like to achieve the following:

    In a field named Comment, I like to search for the starting position of a string (in field ProjectKey cancetenated with a hyphen) and substring the comment string form that position on to the length of another field (IssueKey). To complicate the needed functionality, I'd like to exclude this where the projectkey (concatenated with a hyphen) is not found within the comment string.

    In SQL Server I can create function, which does the trick, but I'd like to incorporate this within a PDI-step
    CASE WHEN CHARINDEX(ProjectKey + '-', Comment) = 0 THEN NULL ELSE SUBSTRING(Comment, CHARINDEX(ProjectKey + '-', Comment), { fn LENGTH(IssueKey) }) END) As DuplicateKey

    How to do this?

    Thanks in advance

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    I would use Java expressions due to dynamic substring length.
    Attached Files Attached Files
    So long, and thanks for all the fish.

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.