"float;#22.0000000000000" Number Returned From CAML Query

Question

I'm running a CAML query to retrieve list items. Some of the columns retrieved contain decimal numbers. The problem is the values coming back from the query look like float;#22.0000000000000. I just need 22.0.

I've tried using ToString(), Decimal.Parse, casting with (float), (double), etc. and they all give me exceptions.

How can I resolve this? Why is this even happening?

CAML query (Approved, Pending and Remaining contain decimal numbers):

SPQuery qry = new SPQuery();
qry.Query = String.Format(
@"   <Where>
    <And>
        <Eq>
            <FieldRef Name='Person' />
            <Value Type='Integer'>
                <UserID />
            </Value>
        </Eq>
        <Or>
            <Eq>
                <FieldRef Name='Year' />
                <Value Type='Choice'>{0}</Value>
            </Eq>
            <Eq>
                <FieldRef Name='Year' />
                <Value Type='Choice'>{1}</Value>
            </Eq>
        </Or>
    </And>
</Where>", currentYear.ToString(), (currentYear + 1).ToString());
qry.ViewFields = @"<FieldRef Name='Approved' /><FieldRef Name='Pending' /><FieldRef Name='Remaining' /><FieldRef Name='Year' />";
SPListItemCollection items = leaveEntList.GetItems(qry);

Answer 1

The easiest way to get a string for conversion is to use SPListItem. the item.GetFormattedValue("ColumnName")

Go though the mentioned post, it could be work for you.

How to convert a decimal number field to C#

Answer 2

This is how SharePoint stores the data internally. You need to use String manipulation for extracting the data.

var data = "float;#22.0000000000000";
//Option 1 - Replace float;# with empty string
string result = data.Replace("float;#", string.Empty);
//Now convert to decimal
decimal d = decimal.Parse(result);

//Option 2 - Using index of #
string result = data.SubString(0, data.IndexOf("#"));
//Now convert to decimal
decimal d = decimal.Parse(result);

Answer 3

If your field type is CALCULATED, it is a known behaviour to solve this you can use SPFieldCalculated object example:

SPFieldCalculated cf = (SPFieldCalculated)item.Fields["AverageFeedBackRating"];

string cfVal = cf.GetFieldValueForEidit(item["AverageFeedBackRating"]);