IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

splitting the Full_Name into First_Name & Second_Name


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Fri Mar 28, 2008 6:38 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Fri Mar 28, 2008 6:44 pm
Reply with quote

Do you need SQL query fo this?
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Fri Mar 28, 2008 6:53 pm
Reply with quote

yes,
i need to do it in SPUFI.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Mar 28, 2008 9:28 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia

PostPosted: Sat Mar 29, 2008 1:32 am
Reply with quote

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?
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Sat Mar 29, 2008 3:18 pm
Reply with quote

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.
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 174
Location: Bangalore

PostPosted: Mon Mar 31, 2008 10:32 am
Reply with quote

Here is the query for your Problem

Code:
select  substr( fullname, 1, instr( fullname, '.' ) - 1 ) as Title , 
substr( fullname, instr( fullname, ' ', - 1 ) + 1 ) as L_name , 
substr  ( fullname,    instr( fullname, ' ', + 1 ) + 1,   
instr( fullname, ' ', - 1 ) - instr( fullname, ' ' ) - 1  )
as F_name from  data


Code:
TITLE              L_NAME             F_NAME
------------------ ------------------ ------------------
Mr                 Gautam             Kumar
Mr                 Gupta              Abhishek
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 174
Location: Bangalore

PostPosted: Mon Mar 31, 2008 2:40 pm
Reply with quote

Gautam,

Is your problem resolved by what I have show?
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Mon Mar 31, 2008 3:08 pm
Reply with quote

Shrivatsa,

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

Please add note on this.
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 174
Location: Bangalore

PostPosted: Mon Mar 31, 2008 3:11 pm
Reply with quote

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

This I tested in oracle...
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 174
Location: Bangalore

PostPosted: Mon Mar 31, 2008 3:40 pm
Reply with quote

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...
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Mar 31, 2008 5:16 pm
Reply with quote

you are correct shrivatsa .. LOCATE can be used here
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Mon Mar 31, 2008 5:31 pm
Reply with quote

btw... If I'm not wrong the parameters in LOCATE are just the other way around in order than in INSTR.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Mon Mar 31, 2008 6:28 pm
Reply with quote

I have written a query(complex???) for this.
Code:
SELECT SUBSTR(FULL_NAME, 1, LOCATE('.', FULL_NAME) -1)             AS TITLE, 
     
       SUBSTR(FULL_NAME, LOCATE(' ', FULL_NAME) +1,         
          LOCATE(' ', SUBSTR(FULL_NAME,
          LOCATE(' ', FULL_NAME) +1)))                             AS F_NAME,

       SUBSTR(SUBSTR(FULL_NAME, LOCATE(' ', FULL_NAME) +1),                                                            LOCATE(' ', (SUBSTR(FULL_NAME,                       
          LOCATE(' ', FULL_NAME) +1))) +1)                         AS S_NAME   
                                                                                           
FROM TABLE1;


I kindly request someone to look into this for simplifying the logic.
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Mon Mar 31, 2008 7:17 pm
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Splitting group records based on deta... DFSORT/ICETOOL 8
No new posts Splitting a file into multiple files ... DFSORT/ICETOOL 7
No new posts logically splitting a CICS region - help CICS 3
No new posts Splitting one record in a file to 10 ... DFSORT/ICETOOL 9
No new posts Splitting one row into multiple Rows ... DFSORT/ICETOOL 9
Search our Forums:

Back to Top