String to equation

[Marty Underwood]

Got a quick question that I cannot get to work at work!

I have a field in SQL server that is set as a varchar but contains data like

50 or <2 which is actually a target percentage. I have data in another

table stored as Decimal(14,2) that is the actual percentage. I need to
extract the two and create a conditional formatting result in a datalist.

Basically it would be something like this.

Assume actualpercentage = 30
targetpercentage = >50

If actual percentagetargetpercentage = True then forecolor= "Green"
Else
forecolor = "Red"

In actual terms it would be like
If 30>50 = True then forecolor = "Green"
Else
forecolor = "Red"



Don't worry about setting the color I can get that accomplished if I could
just build the boolean expression. And trust me I know the way it is written
here seems weird but it represents what I need to test. Basically the target
percentage is anything above 50% so I need to test and see if the actual was
greater than the target. However, like I show before the target could be
anything like <2%

Thanks for any help, a function or any mechanism for that matter would be
great.

Marty U

 

[Kevin Spencer]

Hi Mary,

Obviously the first thing to do is to parse your varchar field. You stated
that your column "contains data like >50 or <2" - you need to get more
specific than that, becuase in essence, you have stored 2 different things
in the column, and the first thing you need is to split your string into 2
pieces. The only way to do that is to know what all of the possible values
for the first (comparison operator) is, so that you can identify where
tosplit the data. Of course, this would have been much easier if you had
used 2 columns to store the 2 values; that is good database design. But once
you've identified all the possible values of the first part, you can create
a loop that loops through all of them and uses the index of the last
character to determine where to split the value. Once split into 2 values,
you need to create a loop which selects from various kinds of comparison
operators that correspond to the ones in your list of possibles, and builds
a comparison statement from one of them.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Kevin Spencer]

Hi Marty,

Look at the String.Substring() method. It's overloaded. One version takes
one parameter, which is the starting index of the substring. It reads to the
end of the string. The other takes a second parameter which is the number of
characters to get. So, assuming that your data, as you said, has only 2
single-character comparison operators, you can get the 2 values from it by
using the String.Substring method(). Example:

Dim s As String = "<123"
Dim operator As String = s.Substring(0, 1)
Dim value As Integer = Convert.ToInt32(s.Substring(1))

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

Thanks for the reply Kevin,

The only two comparison operators would be the greater than, less than

operators. I would have split these into two different columns but the
customer never said they would be used for actual comparisons but just a
display mechanism. Now I don't have time to redesign the related objects
that would use the split column.

I had an idea of creating a function that receives 3 items.

Function ShowResult(ActualValue as Decimal, theOperator as String, TargetValue as Decimal)

Dim theResult as Boolean

theResult = ActualValuetheOperatorTargetValue

Select Case theResult

Case "True"
do something
Case "False"
do something

End Select

End Function

I would use a Left(TargetValue, 1) to pass theOperator argument. Can you

look at this theory and give me an idea how I can pass these 3 items into a
function and get the desired result of whether it's true or false.

Thanks again, I don't have time to harp on this since I have a deadline of

Friday and this is just a perk they would like to have.

 

[Marty Underwood]

Sounds good I will look into this tomorrow at work. Oh and by the way I did
split the column into two seperate columns today due to another issue I had
that was not worth the trouble. It was easier to modify six pages of code
and modify the database then to program with the data being combined in the
DB.

Marty U