AS/400 Query – how do you split names in field into seperate fields

AS/400 Query
IBM iSeries
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)

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TomLiotta
    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
    125,585 pointsBadges:
  • TomLiotta
    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
    125,585 pointsBadges:
  • ToddN2000
    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.
    136,240 pointsBadges:
  • TomLiotta
    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
    125,585 pointsBadges:
  • gfprogrammer
    I agree with ToddN2000 on checking names but you also need to check for name suffix such as Jr, Sr, III and so on.
    250 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: