SQL & PL/SQL

Hi,

I am trying to split the words in a string based on the space in the strings.

If the length of any CUST_NAME is greater than 27, I have to fetch it in my first part of the query and the remaining part in my second part of the query using SUBSTR and INSTR combination, as there is a In house tool which accepts these two functions only.

Below is the sample source data

Create Table S_Customer (

CUST_NAME VARCHAR2(100) ) ;

Insert into S_CUSTOMER VALUES('ABM Industries Incorporated') ;

Insert into S_CUSTOMER VALUES('Ace Hardware Corporation') ;

Insert into S_CUSTOMER VALUES('Adelphia Communications Corporation') ;

Insert into S_CUSTOMER VALUES('Apartment Investment and Management Company') ;

Insert into S_CUSTOMER VALUES('American Axle & Manufacturing Holdings, Inc') ;

Insert into S_CUSTOMER VALUES('Burlington Coat Factory Warehouse Corporation') ;

Insert into S_CUSTOMER VALUES('Apartment Investment and Management Company') ;

Insert into S_CUSTOMER VALUES('ABC') ;

COMMIT ;

I am expecting the first part and second part as below.since there is no way of logically splitting the records I am taking the space for logically splitting of records.

I tried using the below functions but the out put is not coming as expected, please suggest how can I achieve this.

For the First Part  SUBSTR( CUST_NAME, 1, INSTR(CUST_ID, ' ' )-1 )

For the Last Part SUBSTR( CUST_NAME, INSTR(CUST_ID, ' ')+1 )

Regards,

Deepti