• 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
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    866.378.3366

    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

  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

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 © 2025 IT Jungle