We help IT Professionals succeed at work.

Parse name in AS400 : looking for UDF

Camillia
Camillia asked
on
1,851 Views
Last Modified: 2020-04-13
In SQL Server, I wrote a function to parse name. For example, if passed : "Jimmy J Jones III"...it parses firstname , middle name, last name and title. Used Reverse, Charindex (built in SQL Server functions)...

I have to do the same in AS400. I found this:
http://www-128.ibm.com/developerworks/db2/library/techarticle/0303stolze/0303stolze1.html

But I dont think AS400 has "Reverse"...anyone has a function to parse name? Or an idea on how to go about doing that in AS400.
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:

Where do I get a list of "string" functions in AS400? I knew about Substr, about length, but didnt know about "posstr".

Found this : http://www.sqlthing.com/AS400_string_scalars.htm

But didnt see "posstr" in it.

I'll try your example. Seems easier than the one I did in SQL Server.


Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Farzadw,

The entire DB2 reference is here:  http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp

Expand on 'Reference' / 'SQL' / 'Functions' / 'Scalar Functions'

More than you ever wanted to read.   :)


Good Luck,
Kent
Dave FordSoftware Developer / Database Administrator
CERTIFIED EXPERT

Commented:
From Kent's link, it looks like that goes to the Linux / Unix / Windows version of DB2, but I believe you're interested in DB2 for iSeries (aka AS/400).

Therefore, the more useful link would be:

http://publib.boulder.ibm.com/iseries/ 

Click on the version and language you're interested in, then open the the PDF of "SQL Reference" and search for "built-in functions". You get the whole list.

HTH,
DaveSlash
CERTIFIED EXPERT

Author

Commented:

 Yes, we have DB2 for iSeries. Thanks.
CERTIFIED EXPERT

Author

Commented:
IBM is a different beast from SQL Server. :-)
Dave FordSoftware Developer / Database Administrator
CERTIFIED EXPERT

Commented:

... and Oracle is a completely different beast altogether.  (a bigger, hairier, and uglier one ... IMHO )
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

Amen, Brother Dave.....

CERTIFIED EXPERT

Author

Commented:
You know what...that would work if I only assume names are like this:
FirstName MiddleName LastName Title

But If I have FirstName LastName: Then LastName will be parsed in "MiddleName" section...
***I need  to:
1. read from right to left, get the Title first (if it's equal to JR or SR)..it's a title (if blank, no title)
 2. Now I have the Title so my next string is :  NAME- Title
 3.  Then I can get the firstname (like that u have):
 4. Now I have Name-FirstName-Title:
              That leaves me MiddleName and LastName. If no SPACE (' '), then i'll just treat it as LASTName.
              Otherwise, I can use your trick to get MiddleName and last name

This worked in SQL Server but I used "Reverse" to see if Title exists.  
**** *********My question is: How can I read from right to left to get the Title...I thought I can try this:
   (Get the RIGHT characters up to the first blank)
 select right(rtrim(name),substr(rtrim(name),1,posstr(name,' ') -1) )  from ....

But RIGHT needs an int as second parameter...

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
i did not understand the format of your data
can you put the format and exactly what you need ?  i did not understand your instructions

there is no way to get around the lack of the reverse function, unless you know for sure how many blanks you expect inside your string, that is, unless you is string has a fixed format

momi
CERTIFIED EXPERT

Author

Commented:
Format of data is like this:
First Middle Last Title
First Last  
First Last Title
First Middle Last
If only ONE word, then it's going to be assumed it's the firstname

In SQL Server version:
I take the Title first : So I find the last chars up to the first blank (from right to left) , if Jr, Sr, etc, then it's title.
 ***now I have Name - Title

FirstName: take first chars from left to right Up to first blank.

***Now I have Name - FirstName - Title.

 I check to see if there's at least ONE space. (to get middle name and lastname)
     If so, I have Middle name. Go up to the first blank and get the middle name.
     Anything else left is Lastname (***now last name can be 2 words, 3 words BUT that's ok...it will be all lumped together under Lastname***)

-- I think what you have is fine but I dont think it will take care of Lastnames that are 2 or 3 words.
my solution will only work for 1 word last name
the algorithym you describe can not be achived using db2 sql only
you will have to write some sort of a procedure or a function
to implement that logic
CERTIFIED EXPERT

Author

Commented:
and yes that's what I want..i want to put it in a function  as I did in sql server:)

I guess I can do what Kent is saying...
use your code...then check for null or blank in the fields and move them around..
Then at the end ...if there's anything else left, treat it as part of lastname and concat it...

Still leaves me with my orig question: how can I read a string from right to left up to the first blank? not doable with posstr and substr?



nop
you will have to use a loop with the right function and get 1 character at a time
(or use a java function and use java code for that')
CERTIFIED EXPERT

Author

Commented:
and one more question; AS400 Function can be done in like RPG or Java, another language besides SQL...correct?
CERTIFIED EXPERT

Author

Commented:
oh, we posted at the same time .
Dave FordSoftware Developer / Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.