Guru: String Manipulation Using SQL
October 24, 2022 Mike Larsen
Recently, I was working with an employee file where first and last name were concatenated with a comma separator. A requirement of the project was to parse the name and populate a different table where first and last name are separate columns. I won’t address the design of the employee file, but I will show how I completed the request.
In Figure 1, I use the position string function via SQL to extract the first name.
Figure 1. Extract first name
In this example, I use the trim, substring, and posstr functions to position to the first character following the comma. In this case, it positions to the first name. For illustration purposes, I’ve hard-coded the strings, but in a production process you’d want to use variables. When I run this example in ACS, I get the result shown in Figure 2.
Figure 2. Result of extracting first name
The next step is to extract the last name as shown in Figure 3.
Figure 3. Extract last name
This statement works much the same as extracting the first name, but here I specify that I want to start the substring from the first position of the string and end the substring at the position prior to the comma. When I run this, I get the result shown in Figure 4.
Figure 4. Result of extracting last name
Now that I have the first and last names, I’ve decided to take this a step further. I thought it would be nice to convert the first and last names to proper case. Again, using SQL, I use the statement shown in Figure 5.
Figure 5. Convert first name to proper case
Here, I convert the first letter of the first name to upper case and concatenate it with the first name starting from position 2 in lower case to form the first name in proper case. The result is shown in Figure 6.
Figure 6. Result of converting first name to proper case
I use the same statement to convert the last name to proper case (Figure 7).
Figure 7. Convert last name to proper case
The result of this statement is shown in Figure 8.
Figure 8. Result of converting last name to proper case
I often use SQL in my RPG programs where it makes sense. I have used the techniques described in this article several times in various projects since working with them over the past few years and have added them to my toolbox.
Mike Larsen is a director of information technology at Auburn Pharmaceutical and an associate of Central Park Data Systems. He has been working with IBM i systems for over 20 years, and specializes in RPG, CL, and SQL and recently has been working with PHP and Python. Current projects have given Mike the opportunity to work with generating and parsing XML and JSON from SQL and consuming SOAP and REST web services. Although his main area of expertise is on IBM i, Mike has a passion for learning other languages and how he can integrate other platforms with IBM i.
RELATED STORIES
Guru: Regular Expressions, Part 1
Guru: Regular Expressions, Part 2
Mike–enjoyed your concise explanation of converting “Last, First” names. Do you have a similar technique for pulling a jobname out of the jobnumber/jobuser/jobname field that is frequently used in IBM i Services? Most of our jobs are run by the same user, so I find it easier to just substring everything after the jobnumber, but occasionally it would be nice to have a straightforward way of getting just the jobname. Thanks!