Hello, I have to substring following string, its one column, into three columns to pass arguments in an api to get invoice number. COMPANY=01^LUMP_SUM_TRANS_ID=2^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=25^ COMPANY=01^LUMP_SUM_TRANS_ID=20^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=1^ Desired result company LUMP_SUM_TRANS_ID MIXED_PAYMENT_ID MIXED_PAYMENT_LEDGER_TRANS_ID 01 2 4637 25 01 20 4637 1 Now you see lump_sum_trans_id has can have 1 or 2 digits and same with Mixed_payment_ledger_trans_id can be 1 or 2 characters. Company and mixed_payment_id is mostly same number of characters. Previously I used decoded with substring on the assumption that all columns are using same characters….. but now situation is changed. I understand regexp_substr or instr can be helpful but I am lost in it. some expert help will save my day :) Thanks

Solved

Substring Keys from Histoy Log tble

6 years ago
Jan 24, 2020 5:45 PM UTC
7 replies
568 views

O

+10

OrangeCloud
Sidekick (Customer)

Hello,

I have to substring following string, its one column, into three columns to pass arguments in an api to get invoice number.

COMPANY=01^LUMP_SUM_TRANS_ID=2^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=25^

COMPANY=01^LUMP_SUM_TRANS_ID=20^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=1^

Desired result

company	LUMP_SUM_TRANS_ID	MIXED_PAYMENT_ID	MIXED_PAYMENT_LEDGER_TRANS_ID
01	2	4637	25
01	20	4637	1

Now you see lump_sum_trans_id has can have 1 or 2 digits and same with Mixed_payment_ledger_trans_id can be 1 or 2 characters.

Company and mixed_payment_id is mostly same number of characters.

Previously I used decoded with substring on the assumption that all columns are using same characters….. but now situation is changed.

I understand regexp_substr or instr can be helpful but I am lost in it. some expert help will save my day :)

Thanks

Best answer by CallumW

REGEXP_SUBSTR:

REGEXP_SUBSTR('COMPANY=01^LUMP_SUM_TRANS_ID=2^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=25^', '\#=([^^]+)', 1,1,NULL,1)

Replace the # with whatever you want to get:

e.g.

REGEXP_SUBSTR('COMPANY=01^LUMP_SUM_TRANS_ID=2^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=25^', '\MIXED_PAYMENT_LEDGER_TRANS_ID=([^^]+)', 1,1,NULL,1)

View original

+16

CallumW
Superhero (Partner)
Answer
6 years ago
Jan 25, 2020 12:10 AM UTC

REGEXP_SUBSTR:

REGEXP_SUBSTR('COMPANY=01^LUMP_SUM_TRANS_ID=2^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=25^', '\#=([^^]+)', 1,1,NULL,1)

Replace the # with whatever you want to get:

e.g.

REGEXP_SUBSTR('COMPANY=01^LUMP_SUM_TRANS_ID=2^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=25^', '\MIXED_PAYMENT_LEDGER_TRANS_ID=([^^]+)', 1,1,NULL,1)

Callum Wells - https://uk.linkedin.com/in/callum-wells - Anthesis https://anthesisglobal.com/

Like

O

+10

OrangeCloud
Author
Sidekick (Customer)
6 years ago
Jan 27, 2020 7:45 PM UTC

Thanks CallumW, that works like a charm.

Like

O

+10

OrangeCloud
Author
Sidekick (Customer)
6 years ago
Jan 29, 2020 10:04 PM UTC

Hi @CallumW

just a quick question,

can you please let me know the reason of using \ with the second argument in the query.

‘\#=([^^]+)’

‘\MIXED_PAYMENT_LEDGER_TRANS_ID=([^^]+)’

Thanks

Like

J

+9

Jur
Hero (Customer)
6 years ago
Jan 30, 2020 7:28 AM UTC

Why not use client_sys.Get_Key_Reference_Value(keys,’LUMP_SUM_TRANS_ID’) for this?

Regards, Jur

Like

O

+10

OrangeCloud
Author
Sidekick (Customer)
6 years ago
Jan 30, 2020 3:10 PM UTC

@Jur

Wow, that's cool, I was not aware of that API. Thanks for pointing. its very helpful and very simple to use :)

Like

+16

CallumW
Superhero (Partner)
6 years ago
Jan 31, 2020 9:02 AM UTC

Hi @CallumW

just a quick question,

can you please let me know the reason of using \ with the second argument in the query.

‘\#=([^^]+)’

‘\MIXED_PAYMENT_LEDGER_TRANS_ID=([^^]+)’

Thanks

Hi,

You don’t actually need the backslash as its only for escaping characters

For example if the text was (MIXED_PAYMENT_LEDGER_TRANS_ID) then you would need \(MIXED_PAYMENT_LEDGER_TRANS_ID\) in the expression. But in this case there are no special characters so you wouldn’t need it.

Regards, Callum

But I would use @Jur’s answer as its easier to read in this case!

Callum Wells - https://uk.linkedin.com/in/callum-wells - Anthesis https://anthesisglobal.com/