Substring Varchar for days, hours, minutes - format varies

[BeckyBoop]

I am pulling in a varchar field for Elapsed Time. The first 3 characters can be the day; or it could be 2 or even 1 character:

Example:

1D:3H:25M
203D:3H:39M

I normally do a substring for days, hours, minutes and can then perform necessary calculations. However, this is the first time I don't have a set number of characters for days (usually 2 characters).

Any suggestions/recommendations on how to break this out so I can perform my calculations?
Thanks in advance,
~Becky

 

[GJParker]

You Could use the substring function in conjunction with the if function e.g.

if(substring(ElapsedTime,2,1) = 'D')then(substring(ElapsedTime,1,1))else(if substring(ElapsedTime,3,1) = 'D')then(substring(ElapsedTime,1,2))else(substring(ElapsedTime,1,3))

This will Test if D is the 2nd or 3rd character and return the corresponding length string else the 1st 3 characters.

You could use similar to retrieve the hours and minutes.

Hope this helps
Gary Parker
Systems Support Analyst

 

[drlex]

Becky,
An alternative would be the functions 'Locate' (Database function) or 'Position', which can be used to return the character position of the 'D' . This in a 'Substring' or 'Left' argument, together with 'String-to-number', will give you a numerical field for Days. A similar argument will provide for the hours and minutes.
Others on this list might advise as to the comparative speeds of retrieval and processing of these functions as against 'If' arguments that Gary has suggested.
regards,
lex

 

[griffindm]

Becky,

You could try using the position() function. This returns the starting postion of one string within another. The format for your string would be:

Days = string_to_number(substring(elapsed,1,position('D',(elapsed)-1))...

This will get you the number of days a little more tersely than Gary's example and uses database-only functions.

The calculation of the other numbers (Hours, Minutes) can be done in a similar fashion, but require the use of the position function twice, once to find the location of the strings 'H' or 'M', and again to determine where the previous time variant (Days or Hours) ended in the string.

Hope this helps,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[GJParker]

ah Position().......

so that's what cognos have decided to call this function I missed that one...was looking for Instr() or something similar.

Gary Parker
Systems Support Analyst

 

[BeckyBoop]

Thanks for the suggestions!

Dave, the example you gave helped tremendously. My days are now pulling in exactly how I need them. However, I'm not sure how you mean to use "the position function twice, once to determine the location of the strings 'H' or 'M', and again to determine where the previous time variant ended in the string".

I know I'm missing the 2nd 'position variable' in my expression because I am returning '0D:0' for Hours. I'm not sure how/where I need to place the 2nd position variable.

Thanks once again!
~Becky

 

[griffindm]

Becky,

Because the length of the Day portion of the elapsed string may vary, the starting postion for the Hour string will vary also. By using the Postion function for the 'D' string again in the Hour calculation, you can determine where to start the string for Hours. Ditto for Minutes as well.

Gary,

It's always tough to keep track of the names given to functions as the tools change. The Position function in Impromtpu calls the Instr function in Oracle. The String-to-Number function calls To_Number in Oracle even though the function icon in Impromptu doesn't indicate that it can run on the database. Go figure.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[drlex]

Becky,

Code would be something like this:
Hours = string_to_number(substring(elapsed,position('D', elapsed )+2, position('H',elapsed)-position ('D', elapsed)-1)
Minutes = string_to_number(substring(elapsed,position('H', elapsed )+2, position('M',elapsed)-position ('H', elapsed)-2)

The +2 is assuming there is just a single character (colon) between the 'D' and the hour figure and the -2 similarly.
happy friday!
lex

 

[griffindm]

lex,

Welcome to the forum. Nice code on the Hours and Minutes. Always glad to see another capable and generous hand helping people here.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[BeckyBoop]

And I'd like to thank you all for the suggestions and assistance. Without you and this forum, I'd have many frustrating hours (it's obvious I've been away from developing for a while!)

Thanks everyone and have a great weekend.