how to convert date with string having hours, minutes, seconds into only hours/seconds?

[hosasahana]

hi all,

Can someone help me how do I convert below date format to only hours or seconds (numeric).I tried with substring but some how not achieving the right one.

regards,

Sahana

[edwin]

first you need to profile your data and understand possible scenarios.  if these are the only possible scenarios (esp seconds will always be by itself) there are spaces between days hours and minutes:

load num(Seconds) + num(Minutes)*60 + num(Hours)*60*60 + num(Days)*60*60*24 as Duration, *;
load text,
if(SubStringCount(text,'Seconds')=1, SubField(text,' ',1),0) as Seconds,
if(SubStringCount(text,'Minutes')=1, SubField(text,' ',5),0) as Minutes,
if(SubStringCount(text,'Hours')=1, SubField(text,' ',3),0) as Hours,
if(SubStringCount(text,'Days')=1, SubField(text,' ',1),0) as Days
inline [
text
5 Seconds
3 Days 16 Hours
2 Days 10 Hours 40 Minutes
];