how to extract the ID in between special characters

[Anonymous]

Hi guys,

could you please help me

Comments

1.Found the root cause of "Reachability bill group issue"& documented it. 2.Working on CFO data issue*IM001350816&

1.Done the CFO data issue increamendal load script wise issue*IM001350816& 2.Done the aging no read manufacture code mismatch issue. 3.working on GAS ANL enhancement Poc *ALM# 2720&

Support moitoring process AMI Deployment activity*IM001375780& Order life cycle loading issue

From above comments field I need to extract ID number in between  special characters '* 'and '&' .

Expecting Output:

ID	Comments
IM001350816	1.Found the root cause of "Reachability bill group issue"& documented it. 2.Working on CFO data issue
IM001350816	1.Done the CFO data issue incremental load script wise issue 2.Done the aging no read manufacture code mismatch issue. 3.working on ANL enhancement Poc
ALM# 2720	1.Done the data issue incremental load script wise issue*IM001350816& 2.Done the aging no read manufacture code mismatch issue. 3.working on ANL enhancement Poc
IM001375780	Support moitoring process AMI Deployment activity Order life cycle loading issue

Regards,

Yuvaraj

[avinashelite]

Try like this

TextBetween(Comments,'*','&') as ID,

Replace(Comments,TextBetween(Comments,'*','&'),' ') as Comments

[sasiparupudi1]

load

Replace(Replace(Comments,TextBetween(Comments,'*','&'),''),'*&','') as Comments,

TextBetween(Comments,'*','&') as ID;

Load * Inline

[

Comments

1.Found the root cause of "Reachability bill group issue"& documented it.

2.Working on CFO data issue*IM001350816&

1.Done the CFO data issue increamendal load script wise issue*IM001350816&

2.Done the aging no read manufacture code mismatch issue.

3.working on GAS ANL enhancement Poc *ALM# 2720&

Support moitoring process

AMI Deployment activity*IM001375780&

Order life cycle loading issue

];

[Anonymous]

using inline load it as considered single row so its coming but I am loading in excel I didn't get the

(ALM# 2720) this ID  row in my table and  others ID's came.

[avinashelite · Accepted solution]

Try like this

LOAD ID,

Replace(Comments,TextBetween(Comments,'*','&',3),' ') as Comments

Where len(trim(SubField(ID,'_')))>0;

LOAD SubField(ID,'_') as ID,

Replace(Comments,TextBetween(Comments,'*','&',2),' ') as Comments

;

LOAD

TextBetween(Comments,'*','&')&'_'&TextBetween(Comments,'*','&',2)&'_'&TextBetween(Comments,'*','&',3)&'_'&TextBetween(Comments,'*','&',4) as ID,

Replace(Comments,TextBetween(Comments,'*','&'),' ') as Comments;

LOAD * Inline

[

Comments

1.Found the root cause of "Reachability bill group issue"& documented it.2.Working on CFO data issue*IM001350816&

1.Done the CFO data issue increamendal load script wise issue*IM001350816& 2.Done the aging no read manufacture code mismatch issue.3.working on GAS ANL enhancement Poc *ALM# 2720&

Support moitoring process AMI Deployment activity*IM001375780& Order life cycle loading issue

];

[avinashelite]

[sasiparupudi1]

Please add your excel file

[Anonymous]

Comments:

load

Replace(Comments,'*'&TextBetween(Comments,'*','&')&'&',' ') as Comments,

TextBetween(Comments,'*','&') as ID

;

Load * Inline

[

Comments

'1.Found the root cause of "Reachability bill group issue"& documented it.'

'2.Working on CFO data issue*IM001350816&'

'1.Done the CFO data issue incremental load script wise issue*IM001350816&'

'2.Done the aging no read manufacture code mismatch issue.'

'3.working on GAS ANL enhancement Poc *ALM# 2720&'

'Support monitoring process'

'AMI Deployment activity*IM001375780&'

'Order life cycle loading issue'

];