View previous topic :: View next topic
|
Author |
Message |
ajeshrn
New User
Joined: 25 Mar 2009 Posts: 78 Location: India
|
|
|
|
Hi,
I have a table with a column (COLUMN1) declared as VARCHAR. this field is having some data which has the first name<space>last name<spaces>email as a continuous stream.This is the format which is there. Also there is no specific length for firstname and second name and email, it can vary. Consider ,
As -IS
Code: |
FNAME1 LNAME1 helli@email.com1
Firstname lastname helli@email.com2
Firstname Lname helli@email.com3
FName LastName helli@email.com4
|
where I need to extrcat the record till second till the second space. is this possible
To be
Code: |
FNAME1 LNAME1
Firstname lastname
Firstname Lname
FName LastName
|
This To be should be returned through a mainframe Query, Can any one please advice.I need to extract the records until the second space is encountered. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
well, you could use the following scalar functions on every row:
select substr(var_char_col,1,locate(' ',var_char_col,posstr(var_char_col,' ') + 1)
(above is untested - give me a while and I will test it),
which I would do if I owned IBM stock and wanted my company to buy more iron,
but, if I was trying to do this in an economic fashion for my company,
I would select the complete var_char_col and use the COBOL UNSTRING to find the names and discard the email-address. |
|
Back to top |
|
|
ajeshrn
New User
Joined: 25 Mar 2009 Posts: 78 Location: India
|
|
|
|
Thank you...Will try it from my end too.. |
|
Back to top |
|
|
|