Parse name in AS400 : looking for UDF

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.

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

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

 Yes, we have DB2 for iSeries. Thanks.

IBM is a different beast from SQL Server. :-)

... and Oracle is a completely different beast altogether.  (a bigger, hairier, and uglier one ... IMHO )

Amen, Brother Dave.....

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...

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

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

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')

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

oh, we posted at the same time .