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
|
