View previous topic :: View next topic
|
Author |
Message |
the_gautam
Active User
Joined: 05 Jun 2005 Posts: 165 Location: Bangalore
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
Do you need SQL query fo this? |
|
Back to top |
|
|
the_gautam
Active User
Joined: 05 Jun 2005 Posts: 165 Location: Bangalore
|
|
|
|
yes,
i need to do it in SPUFI. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1042 Location: Richmond, Virginia
|
|
|
|
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 |
|
|
the_gautam
Active User
Joined: 05 Jun 2005 Posts: 165 Location: Bangalore
|
|
|
|
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 |
|
|
shrivatsa Warnings : 1 Active User
Joined: 17 Mar 2006 Posts: 174 Location: Bangalore
|
|
|
|
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 |
|
|
shrivatsa Warnings : 1 Active User
Joined: 17 Mar 2006 Posts: 174 Location: Bangalore
|
|
|
|
Gautam,
Is your problem resolved by what I have show? |
|
Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
Shrivatsa,
I can't find the built-in function INSTR on DB2 V9R1 for Z/OS.
Please add note on this. |
|
Back to top |
|
|
shrivatsa Warnings : 1 Active User
Joined: 17 Mar 2006 Posts: 174 Location: Bangalore
|
|
|
|
oh...I didn't know this..
This I tested in oracle... |
|
Back to top |
|
|
shrivatsa Warnings : 1 Active User
Joined: 17 Mar 2006 Posts: 174 Location: Bangalore
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
you are correct shrivatsa .. LOCATE can be used here |
|
Back to top |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
btw... If I'm not wrong the parameters in LOCATE are just the other way around in order than in INSTR. |
|
Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
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 |
|
|
the_gautam
Active User
Joined: 05 Jun 2005 Posts: 165 Location: Bangalore
|
|
|
|
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 |
|
|
|