I want to retrieve string before 'is' or 'I/S', and string after 'is' or 'I/S'
with TableA as
(
select 'Pen is red' as A FROM DUAL
UNION
select 'PENCIL I/S GREEN' as A FROM DUAL
)
select
?? as stringBefore,
?? as stringAfter
from TableA
the result should be
stringBefore stringAfter
--------------------------------
Pen red
PENCIL GREEN
Thanks a lot!!
oracle version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for HPUX: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production