Inserting a decimal point. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inserting a decimal point.

Author

Topic

er.puneet
Starting Member

6 Posts

Posted - 2009-07-17 : 03:43:14

Hi everyone,
The data in one of the column of table is like:

2499
1599
1849
1689

I want to put a decimal point before last two digits, like:

24.99
15.99
18.49
16.89

Please, help me in this regard.

Puneet Sharma

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 04:19:49

CREATE TABLE #temp(id VARCHAR(4))
INSERT INTO #temp
SELECT 1849 UNION ALL
SELECT 1689 UNION ALL
SELECT 1234 UNION ALL
SELECT 1234

SELECT LEFT(ID,2)+'.'+RIGHT(ID,2) FROM #temp
DROP TABLE #TEMP

-------------------------
R..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 04:28:12

What's the data type ? integer ? varchar ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 04:29:28

quote:
Originally posted by rajdaksha

CREATE TABLE #temp(id VARCHAR(4))
INSERT INTO #temp
SELECT 1849 UNION ALL
SELECT 1689 UNION ALL
SELECT 1234 UNION ALL
SELECT 1234

SELECT LEFT(ID,2)+'.'+RIGHT(ID,2) FROM #temp
DROP TABLE #TEMP

-------------------------
R..

you are assuming the length of the data are all 4 characters. Which may not necessary be.

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 04:38:27

Hi KH..

we can use like this..
SELECT 1849.0/100
SELECT 1849.0/100
SELECT 1689.0/100
SELECT 1234.0/100
SELECT 1234.0/100

-------------------------
R..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 04:44:27

until OP tell us what is the data type of the column, you can't be sure

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 04:45:48

YES

-------------------------
R..

er.puneet
Starting Member

6 Posts

Posted - 2009-07-17 : 05:29:09

datatype is float.

ok i have sorted it out by using the following code in asp.net:-

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page
{
int x1;
protected void Page_Load(object sender, EventArgs e)
{

}
public void Insert_item()
{
if(ViewState!=null)
{
float x3 = (float)ViewState["price"];
SqlConnection con = new SqlConnection("Server=.; Database=eclsc; Trusted_Connection=yes");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "update GLASSWARE set price='"+x3+"' where price='"+x1+"'";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Server=.; Database=eclsc; Trusted_Connection=yes");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "select price from GLASSWARE";
cmd.Connection.Open();
SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
x1 = Convert.ToInt32(rdr["price"]);
float x2 = Convert.ToSingle(x1);
float x3 = x2 / 100;
ViewState["price"] = x3;
Insert_item();
}
cmd.Connection.Close();
}
}

Puneet Sharma

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 05:33:56

If float means. this code is fine..

x1 = Convert.ToInt32(rdr["price"]);
float x2 = Convert.ToSingle(x1);
float x3 = x2 / 100;
ViewState["price"] = x3;
Insert_item();

But we are try to give you SQL SERVER Code..Bcoz you have posted in SQL SERVER Forum..

-------------------------
R..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 05:38:11

quote:
x1 = Convert.ToInt32(rdr["price"]);
float x2 = Convert.ToSingle(x1)

if the data type of the column is float why are you converting to integer ?

And then convert to float again ?

KH
[spoiler]Time is always against us[/spoiler]

er.puneet
Starting Member

6 Posts

Posted - 2009-07-17 : 05:52:24

Hi Khtan,
actually its >> x1 = Convert.ToSingle (rdr["price"]);

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page
{
float x1;
protected void Page_Load(object sender, EventArgs e)
{

}
public void Insert_item()
{
if (ViewState != null)
{
float x3 = (float)ViewState["price"];
SqlConnection con = new SqlConnection("Server=.; Database=eclsc; Trusted_Connection=yes");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "update COOKWARE set price='" + x3 + "' where price='" + x1 + "'";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Server=.; Database=eclsc; Trusted_Connection=yes");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "select price from COOKWARE";
cmd.Connection.Open();
SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
x1 = Convert.ToSingle (rdr["price"]);
float x2 = Convert.ToSingle(x1);
float x3 = x2 / 100;
ViewState["price"] = x3;
Insert_item();
}
cmd.Connection.Close();
}
}

Puneet Sharma

er.puneet
Starting Member

6 Posts

Posted - 2009-07-17 : 05:57:51

Hi Rajdaksha,

I tried it using SQL query, but was unable to do it...So, i did it other way...But i really want to know the method to do it using sql.

Puneet Sharma

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 06:01:35

Hi

Is it useful for you what i have posted.. or you want some think other way..

-------------------------
R..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources