Home » SQL & PL/SQL » SQL & PL/SQL » extract portion of strings (12c)
extract portion of strings [message #664810] Sat, 05 August 2017 07:54 Go to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
Hello experts,

I want to remove the portion of strings from the string having values like below.

COMPANY_ID=C02^PAYROLL_LIST_ID=2017081^
COMPANY_ID=C02^PAYROLL_LIST_ID=201709^
COMPANY_ID=C02^PAYROLL_LIST_ID=2017^
COMPANY_ID=C02^PAYROLL_LIST_ID=2017A^

the desired output i want is
 
C02 AND 2017081
C02 AND 201709
C02 AND 2017
C02 AND 2017A


[Updated on: Sat, 05 August 2017 07:54]

Report message to a moderator

Re: extract portion of strings [message #664811 is a reply to message #664810] Sat, 05 August 2017 07:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
NOTHING is post above shows any relationship to Oracle DB

Explain in words how to decide what characters to keep & which characters are discarded.
from where does "AND" originate?
When can desired out only contain single string like "C01"?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

Re: extract portion of strings [message #664812 is a reply to message #664811] Sat, 05 August 2017 10:47 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
Sorry ! its my mistake , i need to extract first portion and second portion of the string starting after '=' and ending before '^' .


select substr(key_ref,instr(key_ref,'=',1,1)+1, ( instr(key_ref,'^',1,1) - instr(key_ref,'=',1,1))-1 ),
key_ref from    APPROVAL_ROUTING where  key_ref = 'COMPANY_ID=C02^PAYROLL_LIST_ID=2017081^'


Re: extract portion of strings [message #664814 is a reply to message #664812] Sat, 05 August 2017 13:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
SQL> WITH T AS (
  2             SELECT 'C02^PAYROLL_LIST_ID=2017081^' COMPANY_ID FROM DUAL
  3            )
  4  SELECT  REGEXP_SUBSTR(COMPANY_ID,'^[^^]+') C1,
  5          REGEXP_SUBSTR(COMPANY_ID,'=([^^]+)',1,1,NULL,1) C2
  6    FROM  T
  7  /

C1  C2
--- -------
C02 2017081

SQL>

SY.
Re: extract portion of strings [message #664822 is a reply to message #664814] Sat, 05 August 2017 22:23 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks Solomon,but actually my string is starting from 'COMPANY_ID=' as given below.


WITH T AS (
               SELECT 'COMPANY_ID=C02^PAYROLL_LIST_ID=2017081^' COMPANY_ID FROM DUAL
              )
    SELECT  REGEXP_SUBSTR(COMPANY_ID,'^[^^]+') C1,
            REGEXP_SUBSTR(COMPANY_ID,'=([^^]+)',1,1,NULL,1) C2
      FROM  T

C1               C2
---              -------
COMPANY_ID=C02    C02



[Updated on: Sat, 05 August 2017 22:32]

Report message to a moderator

Re: extract portion of strings [message #664823 is a reply to message #664822] Sun, 06 August 2017 07:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
WITH T AS (
               SELECT 'COMPANY_ID=C02^PAYROLL_LIST_ID=2017081^' COMPANY_ID FROM DUAL
              )
    SELECT  REGEXP_SUBSTR(COMPANY_ID,'=([^^]+)',1,1,NULL,1) C1,
            REGEXP_SUBSTR(COMPANY_ID,'=([^^]+)',1,2,NULL,1) C2
      FROM  T
/

C1  C2
--- -------
C02 2017081

SQL>

SY.
Re: extract portion of strings [message #664861 is a reply to message #664823] Tue, 08 August 2017 23:09 Go to previous message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks Solomon.
Previous Topic: SQL QUERY
Next Topic: Rectify the code
Goto Forum:
  


Current Time: Fri Mar 29 07:40:15 CDT 2024