Select record that has two digits after decimal point using C# and VB.Net and SQL Server

Hi nagaraju60,

Refer the below sample.

HTML

<asp:GridView runat="server" ID="GridView1" Caption="<b>Input</b>" />
<br />
<asp:GridView runat="server" ID="GridView2" Caption="<b>Output</b>" />
<br />
<asp:Button Text="Get 2 Decimal Place record" runat="server" OnClick="GetData" />

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[1] { new DataColumn("Id", typeof(decimal)) });
        dt.Rows.Add(3.14);
        dt.Rows.Add(5.999);
        dt.Rows.Add(9.555);
        dt.Rows.Add(8.55);
        GridView1.DataSource = dt;
        GridView1.DataBind();
        ViewState["Data"] = dt;
    }
}

protected void GetData(object sender, EventArgs e)
{
    DataTable dt = ViewState["Data"] as DataTable;
    DataTable final = dt.Clone();
    foreach (DataRow dr in dt.Rows)
    {
        decimal d = Convert.ToDecimal(dr["Id"]);
        int count = BitConverter.GetBytes(decimal.GetBits(d)[3])[2];
        //or
        //int count = d.ToString().Split('.').Length > 1 ? d.ToString().Split('.')[1].Length : 0;
        if (count <= 2)
        {
            DataRow drFinal = final.NewRow();
            drFinal["id"] = d;
            final.Rows.Add(drFinal);
            final.AcceptChanges();
        }
    }
    GridView2.DataSource = final;
    GridView2.DataBind();
}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs)
	If Not Me.IsPostBack Then
		Dim dt As New DataTable()
		dt.Columns.AddRange(New DataColumn(0) {New DataColumn("Id", GetType(Decimal))})
		dt.Rows.Add(3.14)
		dt.Rows.Add(5.999)
		dt.Rows.Add(9.555)
		dt.Rows.Add(8.55)
		GridView1.DataSource = dt
		GridView1.DataBind()
		ViewState("Data") = dt
	End If
End Sub

Protected Sub GetData(sender As Object, e As EventArgs)
	Dim dt As DataTable = TryCast(ViewState("Data"), DataTable)
	Dim final As DataTable = dt.Clone()
	For Each dr As DataRow In dt.Rows
		Dim d As Decimal = Convert.ToDecimal(dr("Id"))
		Dim count As Integer = BitConverter.GetBytes(Decimal.GetBits(d)(3))(2)
                'or
                'Dim count As Integer = If(d.ToString().Split("."C).Length > 1, d.ToString().Split("."C)(1).Length, 0)
		If count <= 2 Then
			Dim drFinal As DataRow = final.NewRow()
			drFinal("id") = d
			final.Rows.Add(drFinal)
			final.AcceptChanges()
		End If
	Next
	GridView2.DataSource = final
	GridView2.DataBind()
End Sub

Output

Thanks for giving reply @dharmendr,

it is okay for programming side, but my requirement is in sql query. could you please explain me with best example.

Hi nagaraju60,

Refer the below sample query.

-- For Varchar DataType
DECLARE @Test AS TABLE(Number VARCHAR(50))
INSERT INTO @Test VALUES('3.14')
INSERT INTO @Test VALUES('5.999')
INSERT INTO @Test VALUES('9.555')
INSERT INTO @Test VALUES('8.55')

SELECT Number
FROM @Test 
WHERE LEN(PARSENAME(Number,1)) < 3

-- For Decimal DataType
DECLARE @Test AS TABLE(Number DECIMAL(18,17))
INSERT INTO @Test VALUES(3.14)
INSERT INTO @Test VALUES(5.999)
INSERT INTO @Test VALUES(9.555)
INSERT INTO @Test VALUES(8.55)

SELECT CAST(REPLACE(RTRIM(REPLACE(CAST(CAST(Number AS DECIMAL(18,17)) AS NVARCHAR(MAX)),'0',' ')),' ','0') AS FLOAT) Number
FROM @Test 
WHERE LEN(PARSENAME(CAST(REPLACE(RTRIM(REPLACE(CAST(CAST(Number AS DECIMAL(18,17)) AS NVARCHAR(MAX)),'0',' ')),' ','0') AS FLOAT),1)) < 3