• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • INSERT to Overlay Positions in a Character String

    July 20, 2011 Skip Marchesani

    In V5R3, IBM simplified the manipulation of character stings with the implementation of the INSERT and REPLACE functions in SQL. INSERT allows the positional insertion of one or more characters into a string and REPLACE scans for all occurrences of a target string and overlays or replaces the target string with a replace string. This article will discuss INSERT, and a future article will discuss REPLACE.

    Prior to the implementation of INSERT in V5R3, the positional insertion or update of one or more characters (alpha or numeric) in a character string was a challenge. You had to create a compound SQL statement that split the string into the appropriate pieces with SUBSTR and inserted the desired character(s) with CONCAT. This SQL statement was complex requiring nested or cascading SUBSTRs and CONCATs.

    To illustrate this complexity, let’s look at an example of inserting two hyphens into a 10-position numeric phone number, to make it easier to read in the result set for a SELECT statement.

    The desired end result is to take the 10-position numeric phone number in the format of AAAEEENNNN, where AAA = area code, EEE = phone exchange, and NNNN = phone number; and insert two hyphens into it, transforming it into a 12-position character phone number in the format of AAA-EEE-NNNN.

    Note the phone number without the hyphens has the area code (AAA) in positions 1 to 3, the phone exchange (EEE) in positions 4 to 6, and the phone number (NNNN) in positions 7 to 10.

    The SQL syntax to do this transformation looks as follows;

    SELECT  fname,  lname,
                CONCAT(CONCAT(CONCAT(CONCAT(
                                 SUBSTR(CAST(phone  AS  CHAR(10)),1,3), '-'),
                          SUBSTR(CAST(phone  AS  CHAR(10)),4,3)),  '-'),
                   SUBSTR(CAST(phone  AS  CHAR(10)),7,4))  AS phonea
             FROM  namemstr
    

    The above complex SQL syntax can be simplified to an extent by taking advantage of the implicit cast of data type (no CAST of phone required) that was also introduced in V5R3, and the double pipe ( || ) shorthand for CONCAT and shown below. (See my article, SQL Implicit Cast of Character Strings and Numeric Values, for detailed information on implicit cast of data type.)

    SELECT  fname,  lname,
                     SUBSTR(phone, 1,3) || '-' ||
                     SUBSTR(phone, 4,3) || '-' ||
                     SUBSTR(phone, 7,4))  AS phonea
              FROM  namemstr
    

    In either of the above examples the SQL syntax requires the use SUBSTR and CONCAT to break apart the phone number in the appropriate places and insert the two hyphens.

    Now let’s do this same transformation of the phone number using the INSERT function and see how it simplifies the task. But before we do that, we need to review INSERT to understand how it works.

    The INSERT function allows you to insert a string of one or more characters (alpha or numeric), called the insert string, into a source or a target string and optionally overlay one or more characters in the target string with the insert string. The INSERT function specifies the target string for the insert operation, the position in the target string to begin the insert, the number of positions (if any) to be overlaid by the insert string, and the actual insert string to be inserted.

    To net this out, INSERT provides a simple way to do a positional insert or update in a character string. The syntax for the INSERT function is:

    INSERT(target-string, start-position, overlay-length, insert-string)
    

    Now let’s use INSERT in place of SUBSTR and CONCAT to insert the two hyphens into the phone number. Note that we are only inserting two hyphens and not overlaying any characters in the phone number. Therefore the overlay length must be 0 (zero).

    Note the phone number with the hyphens has the area code (AAA) in positions 1 to 3, the first hyphen in position 4, the phone exchange (EEE) in positions 5 to 7, the second hyphen in position 8 and the phone number (NNNN) in positions 9 to 12.

    The SQL syntax with the CAST explicitly stated looks as follows.

    SELECT  fname,  lname,
                       INSERT(
                            INSERT(CAST(phone  AS  CHAR(10)), 4, 0, '-' ),
                                 8, 0, '-' )  AS phonea 
              FROM  namemstr
    

    And, the SQL syntax with implicit cast looks like this.

    SELECT fname, lname,
                       INSERT(
                             INSERT(phone, 4, 0, '-' ),
                                  8, 0, '-' )  AS phonea
                FROM namemstr
    

    There are two INSERTS, one nested inside the other, because two hyphens need to be inserted into the phone number. The inner (right most) INSERT is done first with that hyphen inserted into position 4 of the phone number with the following result: AAA-EEENNNN. The outer (left most) INSERT is then done with that hyphen inserted into position 8 of the phone number with the following result: AAA-EEE-NNNN.

    In either case (explicit or implicit cast of data type), the SQL syntax that uses INSERT is inherently simpler than the SQL syntax that uses SUBSTR and CONCAT.

    Remember that the end result of the INSERT is a 12-position character column or string, which was transformed from a 10-position numeric column or string.

    Next let’s look at an example of INSERT using an overlay length other than zero. In the following table, the column NAME is 10 positions long, and we want to overlay positions 4 and 5 with the string xxx. The target string is NAM, the start position is 4, the overlay length is 2, and the insert string is xxx.

    Nbr	Nam
    10	Ed
    20	Heikki
    30	John
    40	Mike
    50	Marcela
    60	Frank
    

    The SQL syntax to do the INSERT with overlay is shown below followed by the result of the INSERT.

    SELECT  nbr,  nam,  INSERT(nam,  4,  2,  'xxx')  AS  insrt  
         FROM  emp  ORDER BY  nbr;
    
    Nbr	Nam	Insrt
    10	Ed	Ed xxx
    20	Heikki	Heixxxi
    30	John	Johxxx
    40	Mike	Mikxxx
    50	Marcela	Marxxxla
    60	Frank	Fraxxx
    

    Note that the insert string does not have to be the same length as the overlay length: it can be longer or shorter. When the insert string is shorter than the overlay length, the net effect is the deletion of the number of characters specified in the overlay length–beginning at the start position and then insertion of the characters in the insert string–beginning at the start position.

    Also note that there is no truncation of leading or trailing blank characters when using INSERT. In the above example, the name Ed is only two characters with eight trailing blanks (NAM is 10 positions long). When xxx was inserted into position 4 of the name Ed, the leading blank in position 3 of Ed remains and was not truncated.

    Some additional considerations you should be aware of:

    The result of an INSERT function is the target string, with the insert string placed into the target string (from left to right) beginning at the start position, with the number of characters specified in the overlay length being overlaid in the target string.

    The length (number of positions) of the result of an INSERT function is the length of the target string, plus the length of the insert string, minus the number of positions that were overlaid. The length of the result of an INSERT cannot exceed the maximum length for the data type of the target string.

    INSERT provides a very easy way to do a positional insert into a character string. And, with the optional capability to overlay, it also provides a very easy way to do a positional update.

    Skip Marchesani retired from IBM after 30 years and decided to pursue a career as a consultant in the IBM i marketplace. He spent much of his IBM career working with the Rochester Development Lab on projects for S/38 and AS/400, and was involved with the development of the AS/400. Skip was part of the team that taught early AS/400 education to customers and IBM lab sites worldwide. Now recognized as an industry expert on DB2 for i (a.k.a. DB2/400), and author of the book “DB2/400: The New AS/400 Database,” Skip specializes in providing customized education for any area of the IBM i and AS/400, does database design and design reviews, and general IBM i, iSeries, and AS/400 consulting. He has been a speaker for user groups, technical conferences, and IBM i, iSeries, and AS/400 audiences around the world, and has received COMMON’s Distinguished Service Award. Skip Recently moved to Vermont and is now president of the Vermont Mid Range User Group (VTMUG). Send your questions or comments for Skip to Ted Holt via the IT Jungle Contact page.

    RELATED STORY

    SQL Implicit Cast of Character Strings and Numeric Values



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Fresche Solutions

    Move Projects Forward with Expert Staffing Services

    Gain access to IBM i experts to move IT projects forward, reduce backlog and support business-critical systems.

    Fast onboarding, flexible engagement models for IBM i, RPG, COBOL, CA 2E (Synon), ERPs and more:

    • Bug Fixes & Maintenance
    • Full-Stack Web and Mobile Development
    • Application Enhancements
    • Application Maintenance
    • Database Modernization

    Speak to an Expert »

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Data Storage Corporation:  FREE white paper: 8 reasons to persuade your boss to invest in a DR
    New Generation Software:  Leverage POWER7 to do more than run your existing applications
    Bytware:  Viruses? You'll Never Know Unless You Scan. FREE Webinar. July 20

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    SAP Wants Oracle’s TomorrowNow Award Cut Way Down Top Concern for i Shops: Making Users Happy

    Leave a Reply Cancel reply

Volume 11, Number 21 -- July 20, 2011
THIS ISSUE SPONSORED BY:

WorksRight Software
SEQUEL Software
System i Developer

Table of Contents

  • INSERT to Overlay Positions in a Character String
  • Emulate RPG’s Pessimistic Locking in SQL
  • Admin Alert: Porting an Image Catalog Between Power i Boxes using FTP

Content archive

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

Recent Posts

  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13
  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12

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