• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Remove Extra Blanks, Or Why I Attend Conferences

    October 28, 2019 Ted Holt

    Have you been to a conference lately? If not, you may be shortchanging yourself. I attend several conferences every year and I get immense benefit from them. I learn a lot, I get a break from the day-to-day, and best of all, I build relationships with other people.

    I recently attended the RPG & DB2 Summit in Minneapolis, where I met a bright young developer named Kevan Robinson. He was kind enough to share his version of a tip that I shared with him and other attendees. It’s a technique that I learned ages ago from Craig Mullins, a mainframe expert. It goes like this:

    Suppose you have a character field that contains both a first and a last name, and that both names are aligned with their counterparts in other rows. For example, first names begin in position 1 and last names in position 12.

    Sam         Sung
    Willie      Makit
    Betty       Wont
    

    How can we remove the extra blanks following the first name, leaving only one blank to separate the first and last names?

    Sam Sung
    Willie Makit
    Betty Wont
    

    We do it like this:

    SELECT REPLACE(
              REPLACE(
                 REPLACE(name, ' ', '<>'),
                 '><', ''),
              '<>', ' ')
        FROM MyData
    

    I wish I could find a better way to format that. It’s a bit tough to read. Here’s how it works.

    The third REPLACE is innermost, so it runs first, replacing each blank with a <> pair. The second REPLACE replaces all the >< pairs with nothing, leaving the first < and the last >. The first REPLACE runs last, replacing <> with one blank. I admire this technique. I am amazed at how clever human beings can be.

    Kevan was kind enough to email me and point out that nowadays we can use a regular expression to accomplish the same thing.

    SELECT REGEXP_REPLACE(name, ' +', ' ')
        FROM MyData
    

    Kevan wrote: “The second parameter to REGEXP_REPLACE (space followed by plus) matches sequences of one or more spaces, and the third parameter replaces each of them with one exactly one space.”

    I have shared this technique with others many times, and it never occurred to me to use a regular expression. Like many other people — maybe everybody — I get stuck in old ways. (Not that I consider Mr. Mullins’ technique obsolete. I don’t.)

    Kevan added more food for thought: “Changing the second parameter to ‘\s+’ will also match other kinds of white space, such as newlines and tabs, and replace them with a single space. This might change the string too much for some cases, but I’ve found it useful for normalizing certain kinds of legacy data, such as comment fields on a web app.”

    I could have stayed home — I have plenty of work to do — but I’m glad I attended the conference. Even if I hated conferences, I would have to go anyway. The benefits are too great.

    RELATED STORIES

    A Few SQL Tips and Techniques by Craig S. Mullins

    REGEXP_REPLACE (IBM Knowledge Center)

    Native Regular Expressions In DB2 For i 7.1 And 7.2

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, FHG, Four Hundred Guru, IBM i, RPG & DB2 Summit

    Sponsored by
    IBM

    Refurbish, Reuse, Recycle: A Sustainable approach for IT

    62% of executives considering a sustainability strategy find it essential in order to be competitive (1). As organizations focus more on sustainability, the utilization of remanufactured IT continues to grow in relevance.

    When off-lease IBM® assets cannot be directly reused, they are refurbished, recycled or remanufactured through rigorous processes and original manufacturing standards, and then resold as IBM Certified Pre-Owned servers, storage, parts and features.

    Accelerate your hybrid cloud and AI project with IBM Certified Pre-Owned while helping meet your environmental goals.

    Explore our inventory of IBM Certified Pre-Owned servers, storage, parts and features.

     

    (1) Sustainability as a business strategy IBM Institute for Business Value

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: Quitting Time Wanted: A Real ROI Study For Midrange Platforms

    Leave a Reply Cancel reply

TFH Volume: 29 Issue: 63

This Issue Sponsored By

  • New Generation Software
  • Maxava
  • iTech Solutions
  • WorksRight Software
  • ProData Computer Services

Table of Contents

  • How The Latest TRs Bolster HA/DR And Security
  • Wanted: A Real ROI Study For Midrange Platforms
  • Guru: Remove Extra Blanks, Or Why I Attend Conferences
  • As I See It: Quitting Time
  • Big Power8 Iron Gets A Reprieve, And More Power To You

Content archive

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

Recent Posts

  • Need An RPG Programmer? Nalashaa May Have You Covered
  • Every Day Has To Be Earth Day
  • Guru: Compare Pieces Of Source Members
  • As I See It: Ambivalence
  • IBM i PTF Guide, Volume 23, Number 16
  • Query Supervisor Gives Database Engineers New Power
  • IBM Unveils New and Improved IBM i Services
  • 3 Takeaways from the 2021 PowerTech Security Report
  • Four Hundred Monitor, April 14
  • IBM i PTF Guide, Volume 23, Number 15

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