splitting the Full_Name into First_Name & Second_Name

the_gautam

I ve got a table with the Column as Full_name. i need to Split the data in this column into three parts - "Title", "First_Name" and "Second_Name"

The data goes like :

Full_Name
Mr. Kumar Gautam
Mr. Abhishek Gupta

I need it to be:

Title---F_Name---S_Name
Mr-----Kumar-----Gautam
Mr-----Abhishek--Gupta

here the problem is that the length of the F_Name and S_Name in the actual data is not fixed. the Title length was fixed so i got it using SUBSTR.

Gnanas SNG · Posted: Fri Mar 28, 2008 6:44 pm Post subject:

Do you need SQL query fo this?

the_gautam · Posted: Fri Mar 28, 2008 6:53 pm Post subject:

yes,
i need to do it in SPUFI.

dick scherrer · Posted: Fri Mar 28, 2008 9:28 pm Post subject:

Hello,

I'd suggest you consider something other than an sql query to meet your requirement.

The reason is that after working with many files containing names, it never happens that every name in the file is of a consistent format. Some will not have the "title". Some first-names have 2 (or more) "pieces", some last names have 2 (or more) "pieces", some have middle initials and so on.

Phrzby Phil · Posted: Sat Mar 29, 2008 1:32 am Post subject:

Dick is right - sometimes you just have to (I hope you're sitting down for this ..........) write a program.

You also might have suffixes - JR, III. Are you leaving those with the last name? Any middle intitials? Who/what controls these values?

the_gautam · Posted: Sat Mar 29, 2008 3:18 pm Post subject:

my task was to get the first word as Title, the next as the First_Name and rest of the characters as the Last_Name.
i know that this is an easy task using a COBOL program. but, i had to do it using sql query.
anyways, thanks for the suggestions.

shrivatsa · Active User Joined: 17 Mar 2006 Posts: 153 Location: Pune

Here is the query for your Problem

shrivatsa · Active User Joined: 17 Mar 2006 Posts: 153 Location: Pune

Gautam,

Is your problem resolved by what I have show?

Gnanas SNG · Posted: Mon Mar 31, 2008 3:08 pm Post subject:

Shrivatsa,

I can't find the built-in function INSTR on DB2 V9R1 for Z/OS.

Please add note on this.

shrivatsa · Active User Joined: 17 Mar 2006 Posts: 153 Location: Pune

oh...I didn't know this..

This I tested in oracle...

shrivatsa · Active User Joined: 17 Mar 2006 Posts: 153 Location: Pune

Gnanas,

I feel LOCATE is the function which will work as INSTR in DB2.
Can you please try and let me know...

I don't have Access to RUN...

ashimer · Active User Joined: 13 Feb 2004 Posts: 113

you are correct shrivatsa .. LOCATE can be used here

acevedo · Active User Joined: 11 May 2005 Posts: 252 Location: Spain

btw... If I'm not wrong the parameters in LOCATE are just the other way around in order than in INSTR.

Gnanas SNG · Posted: Mon Mar 31, 2008 6:28 pm Post subject:

I have written a query(complex???) for this.

the_gautam · Posted: Mon Mar 31, 2008 7:17 pm Post subject:

hi shrivatsa,
i got your reply that u tested in oracle. i ve got some other query as well in oracle and mySql. however, my requirement was for DB2.

i got the query from gnanas as well. i ll try this tomorrow as now i am away from my terminal.