How to get particular text from the full text using substring or other function?

[udaya_kumar]

Hi,

I have a field by name A_name, which has data like

market name1 product name1 TRx Volume,

market name2 product name2 TRx Share,

market name3 product name3 product NRx Volume,

market name1 product name2 product name1 NRx Share

Now i want to take only TRx Volume, TRx Share, NRx Volume, NRx Share from those data separately.

how we can get that text from the data?

[deepakk]

hi,

Use the subfield function as below

subfield(A-name,' ',4)

Deepak

[udaya_kumar]

Hi Deepak,

Thanks for reply,

I treid this, but the problem is, i want to get TRx Volume.

I tried this, for some data, it has many words like

market name1 product name1 market name2 product name3 TRx Volume.

in this case, it is giving me 'market' as the result for ur expression.

[Jason_Michaelides]

Just change the third parameter:

SubField(A-name,' ',6) - this will get the 6th bit of the string when broken up by spaces.

Bear in mind that NRx Share values and TRx Volume values will all be mixed in together by this method.  Maybe add another field so you'll have:

Data:

LOAD

     SubField(A-name,' ',5)     AS     TxType //this will give TRx or NRx

     ,SubField(A-name,' ',6)    AS     TxValue //this will give the Share or Volume number

     etc

FROM....;

Hope this helps,

Jason

[udaya_kumar]

Hi Jason,

thanks for reply,

My problem is that, the data is not having fixed no. of words.

it differs for every row like

market name1 product name1 TRx Volume

market name2 product name2 market name2 product name1 TRx Share

.....

In this case, the subfield(field,'5') will give Volume but in second data, it will give name2.

so that is the problem i cannot use subfield.

Is there any function to match data like Start from TRx or NRx and take till end of that like Volume or Share?

[Jason_Michaelides · Accepted solution]

Ah, I see.  In that case, you could use Mid() with FindOneOf() or Indez(), or you could still use SubField() but with a different delimiter:

Data:

LOAD

     SubField(A-name,'TRx ',1)     AS     TRxVolume

     ,SubField(A-name,'NRx ',1)     AS     NRxShare

     etc

FROM....;

Hope this helps,

Jason

[udaya_kumar]

Thank u very much Jason,

I used subfield function to get it.

=subfield(A_NAME,' ',-2) & ' ' & SubField(A_NAME,' ',-1)