Solved

# String Builder

Posted on 2012-08-28

I am trying to build a string based on the column values returned in a result set. Here is part of the result set that is returned.

pmap_no price rank Indicator

6081 69.30 1 Y

6081 69.30 2 N

6081 60.30 3 N

6081 60.30 4 N

6081 60.30 5 N

6081 0.00 6 Y

The string I am trying to build comes from the price column and the indicator column. The string from this above result set would look like ’x-69.3-60.3-60.3-60.3-x

Here are the rules.

Start with the lowest rank.

If indicator value = ‘Y’ then always ‘x’

If the price (money data type) is a whole number such as 12.00 then just the number to the left of the decimal. Such as ‘12’. A value of 0.00 is just ‘0’.

When looking to right of the decimal and greater than 0, follow this pattern.

12.10 = ’12.1’

12.02 = ’12.02’

12.15 = ’12.15’

12.70 = ’12.7’

Finally, between each rank throw in a ‘-‘ to separate the ranks.

Thanks