extract portion of strings [message #664810] |
Sat, 05 August 2017 07:54 |
|
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 #664812 is a reply to message #664811] |
Sat, 05 August 2017 10:47 |
|
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 |
Solomon Yakobson
Messages: 3285 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 |
|
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 |
Solomon Yakobson
Messages: 3285 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.
|
|
|
|