Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

splitting the Full_Name into First_Name & Second_Name

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: splitting the Full_Name into First_Name & Second_Name
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: 785
Location: Chennai, India

PostPosted: Fri Mar 28, 2008 6:44 pm    Post subject:
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    Post subject:
Reply with quote

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

Site Director


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

PostPosted: Fri Mar 28, 2008 9:28 pm    Post subject:
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

Active Member


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

PostPosted: Sat Mar 29, 2008 1:32 am    Post subject:
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    Post subject:
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: 171
Location: Bangalore

PostPosted: Mon Mar 31, 2008 10:32 am    Post subject: Reply to: splitting the Full_Name into First_Name & Seco
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: 171
Location: Bangalore

PostPosted: Mon Mar 31, 2008 2:40 pm    Post subject: Reply to: splitting the Full_Name into First_Name & Seco
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: 785
Location: Chennai, India

PostPosted: Mon Mar 31, 2008 3:08 pm    Post subject:
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: 171
Location: Bangalore

PostPosted: Mon Mar 31, 2008 3:11 pm    Post subject: Reply to: splitting the Full_Name into First_Name & Seco
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: 171
Location: Bangalore

PostPosted: Mon Mar 31, 2008 3:40 pm    Post subject: Reply to: splitting the Full_Name into First_Name & Seco
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    Post subject:
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    Post subject:
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: 785
Location: Chennai, India

PostPosted: Mon Mar 31, 2008 6:28 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Query on Splitting up a file abdulrafi COBOL Programming 19 Wed Apr 20, 2016 3:26 pm
No new posts Splitting file into 5 mallik4u JCL & VSAM 12 Wed Jul 15, 2015 10:13 pm
No new posts Splitting of the text without affecti... mkk157 DFSORT/ICETOOL 4 Wed Feb 18, 2015 6:10 pm
No new posts Splitting a file into four with no di... marc_holmes DFSORT/ICETOOL 9 Thu Jun 12, 2014 1:40 pm
No new posts Splitting 1 dataset into multiple dat... Jay Villaverde JCL & VSAM 18 Wed Apr 16, 2014 9:10 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us