• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: LTRIM + RTRIM > TRIM

    April 2, 2018 Ted Holt

    When IBM enhances SQL for my favorite database management system, I feel so happy I could dance like Karlos Klaumannsmoller selling diabetes medicine. It is my pleasure today to let you know (in case you don’t already know) that IBM has once again enhanced two SQL functions to make them as powerful as their RPG counterparts.

    The functions to which I refer are LTRIM (left trim) and RTRIM (right trim). Until recently these functions could only remove blanks (or hexadecimal zeros, for some data types) from the beginning (left) or end (right) of a string. IBM recently added to these functions a second parameter, with which you can specify a set of characters to be removed from the string. The only catch is that you must run IBM i 7.2 or higher.

    With this enhancement, LTRIM and RTRIM become more powerful than TRIM, which can remove a single character from a string value. For instance, suppose you wish to remove a dollar sign and leading blanks before an amount of money. TRIM cannot do the job, but LTRIM can.

    select c.comment,
           ltrim(comment,' $') as trimmed
      from session.comments as c
    
    Comment Trimmed
    $50 50
      $ 50.21 50.21
      $12.34 12.34

    TRIM has one advantage over LTRIM and RTRIM — it can remove a single character from both ends of the string. LTRIM and RTRIM operate on only one end of the string. However, you can easily circumvent this limitation by nesting LTRIM and RTRIM inside one another.

    select c.comment,
           rtrim(ltrim(comment,' $')) as trimmed
      from session.comments as c
    

    A feature you may have a use for is to specify the list of characters in something other than a literal. In this example, I specify a list of characters in a global variable called NonNumeric.

    create or replace variable MyLib.NonNumeric varchar(256) 
       default 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz $.'
    
    select c.comment,
           rtrim(ltrim(comment,NonNumeric),NonNumeric)
              as trimmed
      from session.comments as c
    
    Comment Trimmed
    $50 50
      $ 50.21 50.21
      $12.34 12.34
    .85 85
      .85 85
    The hat cost $49.99 on sale. 49.99
    Final price is $75 75
    60 goats 60

    TRIM, LTRIM and RTRIM can’t ignore embedded characters, but the REPLACE function can. Let’s get rid of commas, which serve as thousands separators here in the United States of America.

    select c.comment, 
           replace(rtrim(ltrim(comment,' $')),',','')
              as trimmed
      from session.comments as c
    
    Comment Trimmed
    $1,234,567.89 1234567.89
      $ 123,000.21 123000.21

    You might also find regular expressions helpful for retrieving the data you desire from unstructured strings. In this example, I use the REGEXP_SUBSTR (AKA REGEXP_EXTRACT) function to dig out an embedded number.

    select c.comment, 
           dec(regexp_substr(comment, '\d*\.*\d+'),7,2)
              as trimmed
      from session.comments as c
    
    Comment Trimmed
    $50 50.00
      $ 50.21 50.21
      $12.34 12.34
    .85 0.85
      .85 0.85
    The hat cost $49.99 on sale. 49.99
    Final price is $75 75.00
    60 goats 60.00

    The expression looks for zero or more digits (\d*) followed by zero or more periods (\.*) followed by one or more digits (\d+). If you use regular expressions, it may take you a while to produce exactly the right expression for your needs. Regular expressions are almost as cryptic as tax laws.

    Mix and match these functions to get the results you need. There are a lot of ways to remove unwanted characters from strings.

    Thus is proven yet again the ancient dictum: where there’s a will, there’s a function. Or something like that.

    RELATED STORIES

    Enhanced LTRIM and RTRIM built-in functions

    Quick-Start: Regex Cheat Sheet

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: IBM i, LTRIM, RPG, RTRIM, SQL

    Sponsored by
    Software Concepts

    Distribution and financial software that provides efficiency without complexity

    Bigger isn’t always better. Not only are high end “out of the box” packages costly, they just aren’t built to handle the uniqueness of smaller businesses. Rather than investing in a complicated ERP solution with hopes that you’ll grow into it, we’ve architected our DS90 iSeries distribution software solution as an affordable ERP software offering that caters to your increased needs as your business matures.

    Feature-rich functionality

    • Modern Windows-based GUI or traditional character-based green screen
    • Includes key capabilities of high-end ERP applications
    • Adapts to handle the unique workflows of smaller businesses
    • Robust functionality right out of the box
    • Seamless integrations with other technologies and services

    Expert guidance from technology experts – we’ve got your back

    • Efficient, affordable customizations: We’ll offer innovative (yet economical) customizations to support your in-house workflows, improve production and increase yield.
    • Training, support and diagnostics: Everything you need to sleep better at night. Worry about your business goals – not your technology!
    • Responsive, skilled and accessible support: One phone call gets you the answers you need without all the technical jargon. No “blame game” or finger-pointing with us as your solutions provider.

    Software Concepts  978.584.0400

    Products  –  Schedule A Demo  –  Contact

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Power9 Servers Get Legacy Adapter Support Unintended Consequences: When Software Installations Go Off The Track

    Leave a Reply Cancel reply

TFH Volume: 28 Issue: 25

This Issue Sponsored By

  • UCG TECHNOLOGIES
  • WorksRight Software
  • Software Concepts
  • ARCAD Software
  • Manta Technologies

Table of Contents

  • The Five Hottest IBM i RFEs Of The Quarter
  • Unintended Consequences: When Software Installations Go Off The Track
  • Guru: LTRIM + RTRIM > TRIM
  • Power9 Servers Get Legacy Adapter Support
  • Pay Attention To JDK And WebSphere Release Support

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Get Your PHP on IBM i, Hold the Zend
  • Syncsort’s Pitney Bowes Deal: All About Good, Clean Data
  • Three IBM i Vendors Who Are Still Announcing Things in 2019
  • Four Hundred Monitor, December 11
  • IBM i PTF Guide, Volume 21, Number 49
  • Moving Off Big Iron? Be Very Careful, Gartner Says
  • Thoroughly Modern: More Than Just A Pretty Face
  • Guru: More End Of Year Feedback
  • As I See It: When Gates Was Young
  • Servers Cool A Bit In Q3, But The Market Is Still Hot

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2019 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.