• 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
    ARCAD Software

    [Webinar Series] Demystifying DevOps on the IBM i

    Join us for a 3-part “Live” Roundtable webinar series as we demystify DevOps on the IBM i.  We’ll start with discussions on Git and the IBM i and developer tool options.  Next, we will cover Branch Management and Builds. We’ll end with discussions on an Automated DevOps Workflow.  It will be an informative series on modernizing the IBM i development process. Our speakers will address your questions and share their years of experience and best practices.

    Register NOW

    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

  • The IBM i Power10 Upgrade Cycle Forecast Looks Favorable
  • White Hats Completely Dismantle Menu-Based Security
  • Cloud Software To Drive Enterprise Application Growth
  • How Do You Stay In Touch With The IBM i Community?
  • IBM i PTF Guide, Volume 25, Number 6
  • Security Still Top Concern, IBM i Marketplace Study Says
  • Bob Langieri Shares IBM i Career Trends Outlook for 2023
  • Kisco Brings Native SMS Messaging to IBM i
  • Four Hundred Monitor, February 1
  • 2023 IBM i Predictions, Part 4

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.