String to equation

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 UHi 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.

"Marty Underwood" <martman100@.insightbb.com> wrote in message
news:bczYb.341294$xy6.1700666@.attbi_s02...
> 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
>
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.

"Marty U" <anonymous@.discussions.microsoft.com> wrote in message
news:8ABD3BD8-4FD3-456D-8DA0-722D75DCD452@.microsoft.com...
> 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.
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

"Kevin Spencer" <kevin@.takempis.com> wrote in message
news:e2rCPxk9DHA.452@.TK2MSFTNGP11.phx.gbl...
> 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.
> "Marty U" <anonymous@.discussions.microsoft.com> wrote in message
> news:8ABD3BD8-4FD3-456D-8DA0-722D75DCD452@.microsoft.com...
> > 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.