Skip to main content
Solved

Substring Keys from Histoy Log tble

  • January 24, 2020
  • 7 replies
  • 550 views
O
Sidekick (Customer)
Forum|alt.badge.img+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

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)

 

durette
Himasha Abeywickrama
O

7 replies

CallumW
Superhero (Partner)
Forum|alt.badge.img+16
  • CallumW
  • Superhero (Partner)
  • Answer
  • January 25, 2020

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/
durette
C
O
Sidekick (Customer)
Forum|alt.badge.img+10
  • OrangeCloud
  • Author
  • Sidekick (Customer)
  • January 27, 2020

Thanks CallumW, that works like a charm.

O
Sidekick (Customer)
Forum|alt.badge.img+10
  • OrangeCloud
  • Author
  • Sidekick (Customer)
  • January 29, 2020

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
Hero (Customer)
Forum|alt.badge.img+9
  • Jur
  • Hero (Customer)
  • January 30, 2020

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

Regards, Jur
durette
CallumW
O
O
Sidekick (Customer)
Forum|alt.badge.img+10
  • OrangeCloud
  • Author
  • Sidekick (Customer)
  • January 30, 2020

@Jur 

 

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

CallumW
Superhero (Partner)
Forum|alt.badge.img+16
  • CallumW
  • Superhero (Partner)
  • January 31, 2020

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/
O
C
Sidekick (Partner)
Forum|alt.badge.img+4
  • CapAvinaK
  • Sidekick (Partner)
  • March 19, 2021

Very helpful.

Thanks

Terms & ConditionsCookie settingsAccessibility statement