Experts,

I need small help in oracle. I am just putting this question here because i think there are SQL geniuses here as well as UNIX.

I have a string that contains the First name, Middle name and last name in one field in the table in Oracle. I need to extract each one of them seperately. Can any one help with sql for this please.

Field in the table
Required Output:
I will appreciate help in this matter.

Thanks

Hi,

You can post the SQL or Oracle related questions in Oracle forum.

The below query should help you.

SELECT trim(DECODE(ROWNUM,1,SUBSTR(STR,1,INSTR(STR,' ',1)),
                                    LENGTH(STR)-LENGTH(REPLACE(STR,' '))+1,SUBSTR(STR,INSTR(STR,' ',-1,1)),
                                     SUBSTR(STR,INSTR(STR,' ',1,ROWNUM-1), INSTR(STR,' ',1,ROWNUM)-INSTR(STR,' ',1,ROWNUM-1))
                               ))  STR1
                 FROM (SELECT 'Jody E Mcquire' STR FROM DUAL),ALL_TABLES
                 WHERE ROWNUM <= LENGTH(STR)-LENGTH(REPLACE(STR,' '))+1
                  ORDER BY ROWNUM

Try something like....

SQL> SELECT substr('Jody E Mcquire',1,instr('Jody E Mcquire',' ')-1) first FROM dual;

FIRST
-----
Jody

SQL> SELECT substr('Jody E Mcquire',instr('Jody E Mcquire',' ',1,2)+1) last FROM dual;

LAST
-------
Mcquire

Ygor/Mona,

Thank you guys for the replys. But how can i get the Middle initial also. Please help.

Thanks,

You can write your own SQL for the middle initial using the instr and substr functions. Use the above examples for guidance.

Hi,
i need to check my string1 is in another string2.


input_filename=017200910.DC30025
if [ my file has .DC3 ]
then
i need do some action


Can anyone tell me how to check my string is in another string.

example
i need to check .DC3 is in 017200910.DC30025