• 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
    Chilli IT

    Chilli is one of the UKs leading IBM support and management providers with 20 years’ experience in the power and storage industry. Our bespoke solutions for maintenance, security and infrastructure delivers a service which is cost effective, increases productivity and enhances efficiency. Our ethical approach and unrivalled knowledge has secured business partnerships with blue-chip companies in the technology, retail, banking and travel sectors.

    As an IBM Business Partner, we provide you with the peace of mind that you are working in partnership with a company accredited to the highest standard. Our team of experts have worked together for many years and deliver projects which include consolidation, High Availability, Operating System upgrades; and backup and recovery installations.

    Contact us to see how we can help your business with IBM support and management.

    www.chilli-it.co.uk

    info@chilli–it.co.uk

    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

  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050
  • DRV Brings More Automation to IBM i Message Monitoring
  • Managed Cloud Saves Money By Cutting System And People Overprovisioning
  • Multiple Security Vulnerabilities Patched on IBM i
  • Four Hundred Monitor, June 22
  • IBM i PTF Guide, Volume 24, Number 25

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.