AS/400 Query – how do you split names in field into seperate fields
Trying to separate a field into three separate fields. The field is a name field, with lastname firstname mi with spaces in between each name. Is there a way to do this? I've tried everything I can think of, am out of ideas.
Thanks in advance for your help
Software/Hardware used:
iSeries v5.4 AS400 (not SQL)
Software/Hardware used:
iSeries v5.4 AS400 (not SQL)
ASKED:
February 22, 2013 3:56 PM
UPDATED:
February 22, 2013 5:20 PM
Answer Wiki:
You are not going to be able to do this with Query,
You need to use a language that allows for scan and substring.
With the format you defined: LASTNAME, FIRSTNAME MI
Start at pos 1 and scan for ','. When found all data preceding the comma is LASTNAME.
Now start at the position of the comma +1 and scan for a blank.
When found, data from the beginning scan position to the blank is FIRSTNAME.
Data after the blank is MI
To see all answers submitted to the Answer Wiki: View Answer History.
Once again, I entered a comment and, for some unknown reason, it’s not showing up here. Apparently we’ll need to wait until morning to see if the comment gets released. — Tom
Comment hasn’t appeared, so I’ll try again. Since this is tagged “(not SQL)”, I assume you don’t want to use QM Query. Of course, to start, you need three result fields. Let’s call them FN, MI and LN for convenience here. They’re based on a field we’ll call FULLNAME, and they are three substring fields. The result definitions are SUBSTR(fullname, pf, lf), SUBSTR(fullname, pm, lm) and SUBSTR(fullname, pl, ll). The SUBSTR() parameters are result fields indicating position and length for each of the three. But that’s where it gets messy because you tagged this with “(not SQL)”, and I don’t know any way of going farther in any query without SQL (or other programming for OPNQRYF) unless the areas are in fixed positions. Someone else needs to continue with a more complete solution if one exists. Not much help in this comment, but hard to see what help is possible. — Tom
Unless the fields have static lengths, you will have to programatically break the fields out. That may even be a problem if the name portions are only separated with a blank. Comma delimited should have been the way to design the database. Lets say you have a female named “Sue Ann Parker”. How do you grab the correct first name if it has a blank in it ?Everything in this case is off one field position.If the portions of the name are static the in you query you can substring out the individual portions by starting and ending position of each piece.
The simplest way is to use SQL. Even if you are required to use WRKQRY for some odd reason, SQL can make it possible for your WRKQRY queries to access the three sub-fields. Please clarify why SQL is excluded. — Tom
I agree with ToddN2000 on checking names but you also need to check for name suffix such as Jr, Sr, III and so on.