• 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
    Maxava

    Migrating to a new IBM Power System?

    Whether it be Power8, Power9 or Power10 – Maxava has you covered

    Our migration service moves data from the old to the new server without disruption while the business continues to operate without impacting performance. Our service avoids long periods of downtime and means businesses can reduce the risk of moving to new hardware.

    To learn more about Maxava’s migration service, call us on 888 400 1541 or VISIT maxava.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

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

    One thought on “Guru: LTRIM + RTRIM > TRIM”

    • Tim Molter says:
      April 2, 2020 at 4:55 pm

      TRIM now does what LTRIM and RTRIM do by removing all blank characters on both sides of the string. https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1556.htm

      Reply

    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

  • COMMON Set for First Annual Conference in Three Years
  • API Operations Management for Safe, Powerful, and High Performance APIs
  • What’s New in IBM i Services and Networking
  • Four Hundred Monitor, May 18
  • IBM i PTF Guide, Volume 24, Number 20
  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners

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 © 2022 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.