RPG's %XLATE BIF converts characters to uppercase or lowercase. The previous article of this series showed a few SQL functions that provide this type of functionality. But %XLATE can also replace characters in a string; that's what this article is about.

Let me start with a very quick recap: the previous article explained a few functions that cover part of %XLATE's functionality. But LOWER and UPPER can't be used to replace characters in a string; I've shown that you can use TRANSLATE to do that. However, depending on the situation, the input-by-output-character-replacement feature of TRANSLATE can be a blessing or a curse. For instance, if you want to replace a single character with an expression, say three characters long, TRANSLATE won't do. But there's another function that can help: REPLACE.

This function provides the %XLATE and TRANSLATE functions replacement capabilities plus a bonus: you can replace an expression—either a single character or any other string—with another expression of a different length. Note that this functionality comes with at a cost: while TRANSLATE can perform several different substitutions at a time, REPLACE can perform only one type of substitution at a time. Bear with me for a few moments and you'll understand what I mean.

Before I show the REPLACE function in action, let me explain this function's parameters; the first one is the input string, followed by the search string and then by the replacement string.

Now, let's go over a few examples; the first one is a simple character-replacement operation:

SELECT        REPLACE('Test string', 'T', 'B')

FROM         SYSIBM.SYSDUMMY1

This returns 'Best string'; notice that the input and output (or search and replace) strings are back to the familiar %XLATE pattern. Is this too simplistic? Check out the next example:

SELECT        REPLACE('Test string', 'Test', 'Production environment')

FROM         SYSIBM.SYSDUMMY1

The returned value is 'Production environment string'. This shows the beauty of the REPLACE function; you can replace a search string with a different-sized replacement string. Unfortunately, it also shows its major weakness; if you also want to replace the 'i' with '!', you need to execute a second replacement statement, because there's only one search string and one replacement string in the function's parameter list. But there's another cool feature that might be useful: REPLACE also has the ability to selectively delete characters or string expressions. How? Well, it's really simple, as you can see in the following example:

SELECT        REPLACE('Test string', 's', '')

FROM         SYSIBM.SYSDUMMY1

Notice that I'm using an empty string in the replacement string parameter. This will force the replacement of the search string with an empty string—in other words, it will delete every occurrence of the search string, thus producing the 'Tet tring' output. If you want to replace characters at a specific position, REPLACE won't do, because it looks for the search string and replaces it with the replacement string, regardless of the initial position of the search string. But don't despair! There's another function for this: INSERT.

This function also replaces text in a string, but does it by position. You specify values for the function's four parameters: source string, start position, length, and string to insert. How does it work? Simple: INSERT returns a string where length characters have been deleted from the source string beginning at start position and where string to insert has been inserted into the source string, from start position onward. I know this may sound confusing, so let me show you a couple of examples, starting with a simple one:

SELECT        INSERT('This is a test string', 9, 1, 'another')

FROM         SYSIBM.SYSDUMMY1

This statement takes the 'This is a test string' string, cuts the substring that starts in the ninth position with length one (that's the 'a' substring) and inserts the 'another' string in its place. The output is 'This is another test string'. You might be thinking, "It's a bit silly to remove an 'a' to insert a string that also starts with an 'a'." So let's tweak this sample statement a bit:

SELECT        INSERT('This is a test string', 10, 0, 'nother')

FROM         SYSIBM.SYSDUMMY1

The output is the same as before; the difference is that by specifying zero in the length parameter, I'm not actually deleting anything; I'm just inserting 'nother' starting in the 10th position. Now comes the weird part: INSERT can also be used to remove characters! It's simply a question of specifying an empty string in the string to insert parameter:

SELECT        INSERT('This is a test string', 16, 7, '')

FROM         SYSIBM.SYSDUMMY1

The output of this statement is 'This is a test', because it removed seven characters starting at the 16th, or the ' string' substring, and inserted an empty string at that position. In other words, it didn't insert anything; it just removed the last seven characters of the original string.

These last two articles explored SQL functions that can be used to replace RPG's %XLATE; hopefully, they've shown you new ways to pull strings around, in a simpler and easier-to-maintain way than %XLATE. The next article will do the same for the %TRIM BIF. You might think that a trim is just a trim, but there's much more to it than you realize!

Rafael Victória-Pereira has more than 20 years of IBM i experience as a programmer, analyst, and manager. Over that period, he has been an active voice in the IBM i community, encouraging and helping programmers transition to ILE and free-format RPG. Rafael has written more than 100 technical articles about topics ranging from interfaces (the topic for his first book, Flexible Input, Dazzling Output with IBM i) to modern RPG and SQL in his popular RPG Academy and SQL 101 series on mcpressonline.com and in his books Evolve Your RPG Coding and SQL for IBM i: A Database Modernization Guide. Rafael writes in an easy-to-read, practical style that is highly popular with his audience of IBM technology professionals.

Rafael is the Deputy IT Director - Infrastructures and Services at the Luis Simões Group in Portugal. His areas of expertise include programming in the IBM i native languages (RPG, CL, and DB2 SQL) and in "modern" programming languages, such as Java, C#, and Python, as well as project management and consultancy.

---

MC Press books written by Rafael Victória-Pereira available now on the MC Press Bookstore.

		Evolve Your RPG Coding: Move from OPM to ILE...and Beyond Transition to modern RPG programming with this step-by-step guide through ILE and free-format RPG, SQL, and modernization techniques. List Price $79.95 Now On Sale
		Flexible Input, Dazzling Output with IBM i Uncover easier, more flexible ways to get data into your system, plus some methods for exporting and presenting the vital business data it contains. List Price $79.95 Now On Sale
		SQL for IBM i: A Database Modernization Guide Learn how to use SQL’s capabilities to modernize and enhance your IBM i database. List Price $79.95 Now On Sale

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL