Skip to main content

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

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)

 

Thanks CallumW, that works like a charm.

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=(N^^]+)’

 

Thanks

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

@Jur 

 

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

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=(A^^]+)’

 

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!

Very helpful.

Thanks

Reply


Terms & ConditionsCookie settings