Solved

Substring Keys from Histoy Log tble

  • 24 January 2020
  • 7 replies
  • 493 views
O
Rank
Userlevel 4
Badge +10

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

icon

Best answer by CallumW 25 January 2020, 01:10

View original

7 replies

CallumW
Rank
Userlevel 6
Badge +15

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
O
Rank
Userlevel 4
Badge +10

Thanks CallumW, that works like a charm.

O
Rank
Userlevel 4
Badge +10

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

J
Rank
Userlevel 4
Badge +9

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

Regards, Jur
O
Rank
Userlevel 4
Badge +10

@Jur 

 

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

CallumW
Rank
Userlevel 6
Badge +15

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
C
Rank
Userlevel 1
Badge +4

Very helpful.

Thanks

Reply


Terms & ConditionsCookie settings