Substring Query

[keating101]

Hi Guys,

I am designing a report studio report.
There is a single data item which is essentially made up of "Department.CostElement".
For example CAFE.ELEC - Café been the department, ELEC (Electricity) been the cost element.

How do I write some code to split the two - so department and cost element are separate?
The problem is that the character length of department and cost element vary, so I cannot write a straight substring code.

Thanks in advance,
Shaun

[navissar]

That's what the "position" function is for. It returns the first occurrence of a character, in your case, ".".
Consider the two following expressions:

Code Select Expand

substring ([Department.CostElement],1,position ('.',[Department.CostElement])-1)
That will substring from the first position to the position of the first period minus one. In your example, it would be from 1 for (5-1=)4 steps:"CAFE".
Now, this:

Code Select Expand

substring ([Department.CostElement],position ('.',[Department.CostElement])+1)
This will substring from the next character after the period and all the way through the string.

[keating101]

thanks - that worked.
Issue closed