Extract Currency from Sec id

[bubblyjai1982]

Hi All

I'm trying to extract the currency from sec id using regex tool but not able to extract only currency.

I have differentiated the alphabet and date using the expression ([[:alpha:]]+)(\d+) but from alphabet i want to pull only currency which is not at constant place in sec id.

I have attached input data and also required output.... 

Kindly assist 

Thanks in advance 

[apathetichell]

off the top of my head regex_replace([yourfield],'^.*(\w{3})0.*$','$1')

this says give me the 3 letters which come before a  0 (no space). if you had multiple 0 strings...

[caltang]

It's hard to regex it without knowing all the patterns available (at least to me). 

Because the currency is in different positions, we need something else to identify them. Are the first few characters before a currency symbolizing anything of significance or are matching some other column?

[bubblyjai1982]

Yes, the currency is in different position and the characters before currency is not symbolizing anything or matching with any column.

Due to above reason I'm facing challenge in extracting currency from all sec id. 

[davidskaife]

Hi @bubblyjai1982 

If you always have a 0 after the currency this Regex works - it grabs the last 3 letters prior to the 0

(\w{3})0

[caltang]

Well I've got a dumb way to do it...

The idea is to use the REGEX tool to identify the major currencies first. So whatever matches first will be the currency. If it's null, go down the chain of currencies.

It's the only way in my head right now because there's really no pattern to it.

[caltang]

Good eye spotting that pattern @davidskaife !

[bubblyjai1982 · Accepted solution]

Thank you All !!

The logic "0 after currency" does not work, to be clear this is date in format MMDDYY and if it has date from OCT to Dec month which starts with 1, so in this scenario it will not work.

However I have tried other way to extract the currency and it worked

First: differentiated alpha and numeric character 

Secondly: using formula tool pulled last three character 

Thanks

[davidskaife · Accepted solution]

@bubblyjai1982 

In that case simply tweak the Regex slightly to (\w{3})\d

Returns the last three letters prior to a numerical value

[apathetichell]

Hey @bubblyjai1982 did you try my regex replace ---> if you swap the 0 to [0,1] that should fix your issue... would have helped though had you explained that that this was a date --- or included an example with a 1 vs a 0.