Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Best way to add leading 0 to some of the numbers (as string) in a field

  1. #1
    Join Date
    Nov 2013
    Posts
    2

    Smile Best way to add leading 0 to some of the numbers (as string) in a field

    Hi

    Just started using Kettle Yesterday (Love it -- already productive) But I cant figure out a good way to do this:
    The school district I work for uses student ID's in 2 formats:

    Type1 - 20094354 (8 digits always starts with 2)
    Type 2 - 0566677 (7 digits always starts with 05)

    I get many files in Excel, which always "helps out" by stripping off the leading zero. (0566677 becomes 566677)


    What is a good way to make sure that Type2 student IDs always start with a zero in the final output?

    Thanks

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    Stripping ZEROs helps if they are genuine number and not codes, like telephone numbers, card codes and so on!
    if you want to keep the leading 0, then read that field as a String, not as a number.
    (I think that you can have number with leading zeros as well, but I personally never used that option)
    -- Mick --

  3. #3
    Join Date
    Nov 2013
    Posts
    382

    Default

    Is the origin a number? substring(cast((MyNum + 10000000) as char(8)),2,7)

    If it's already a char there are probably more eficient ways, but you can always double cast it!: substring(cast((cast(MyChar as numeric) + 10000000) as char(8)),2,7)

  4. #4
    Join Date
    Nov 2013
    Posts
    2

    Default

    Thanks for the replys everyone!

    In my case I just ended up using the regex evaluator to force my 2 different types of student numbers into 2 different fields using capture groups. Once I had these new fields it became a trivial problem to solve.

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.