substring-urgent

[Sep2013]

hi,

I have a requirement to separate the account no. and account description from a field that has both of them concatenated.

But few of them start with an alphabet and others with a number.

for eg:
CA2147 Suspense Disposal Clearing $0.00 $0.00 -$29,995.50
CA2363 Other Receivables Sundry $441,275.49 $338,323.77 -$1,201,106.22 62063806    921 WIP Interdist Exp Overheads $2,942.73 $0.00 $0.00
62163806    921 WIP Interdist Exp Overheads $45,967.00 $41,391.00 $29,139.75

So the substring function has to be different for both of them.
Do we have any function like in_numeric or something which can detect the first letter to be an alphabet or number and perform the related operation?

[MFGF]

Hmmm. Deja vu. I'm sure I just answered the same post in the Cognos Express forum. Which do you have, Cognos 10 (Enterprise) or Cognos Express?

Here was my reply:

Hi,

There is no direct equivalent to the is_numeric function inbuilt in Express or Cognos 10. Depending on the database you are connected to, there may be a similar function you can call that exists within the database? If not you could try the following approach:

if (left([Your concatenated field],1) in ('0','1','2','3','4','5','6','7','8','9')) then (<whatever you need to do if the first character is numeric>) else (<whatever you need to do otherwise>)