469,591 Members | 2,075 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,591 developers. It's quick & easy.

How to convert from ntext to float?

Hello,

I would like to convert a field from ntext field found in one database
table to float field found in another database table. The reason why I
want to do this is a long one.

I have tried the following and playing around with the following:

declare @valuePointer varbinary(16)

<Row cursor logic to initialize @valuePointer to be a pointer to the
source ntext field>

update TargetFloatTable set TargetFloatTable.TargetFloatValue =
CAST(CAST(@valuePointer AS nvarchar) AS float)
where TargetFloatTable.Id = @Id

but is not working for me.

Hoping someone out there can help.

Thanks,

Cally

Jul 23 '05 #1
5 17710
On 15 Mar 2005 13:35:22 -0800, Cally wrote:
update TargetFloatTable set TargetFloatTable.TargetFloatValue =
CAST(CAST(@valuePointer AS nvarchar) AS float)
where TargetFloatTable.Id = @Id


Try:

update TargetFloatTable set TargetFloatTable.TargetFloatValue =
CAST(CAST(@valuePointer AS nvarchar(255)) AS float)
where TargetFloatTable.Id = @Id
Jul 23 '05 #2
Cally (ze**********@hotmail.com) writes:
I would like to convert a field from ntext field found in one database
table to float field found in another database table. The reason why I
want to do this is a long one.

I have tried the following and playing around with the following:

declare @valuePointer varbinary(16)

<Row cursor logic to initialize @valuePointer to be a pointer to the
source ntext field>

update TargetFloatTable set TargetFloatTable.TargetFloatValue =
CAST(CAST(@valuePointer AS nvarchar) AS float)
where TargetFloatTable.Id = @Id

but is not working for me.


You are trying to convert the text pointer to float - that is not
very likely to succeed. Here is an example that works:

CREATE TABLE nt (a int, n ntext)
go
INSERT nt (a, n) values (1, '9.234E30')
go
select convert(float, convert(nvarchar, n)) from nt

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
Yes, I was doing some funky things with text pointer that was not
going to work.

So based on your suggestion, I tried this:

update TargetTable set TargetTable.TargetFloatValue = (select
convert(float, convert(nvarchar, OtherTable.NTextValue)) from
OtherTable where OtherTable.ID = @id) where TargetTable.ID = @id

And I get the following error:

"Error converting data type nvarchar to float."

Which really surprises me because it is basically what you have
written and because the following works:

updatetext TargetTable TargetTable.NTextValue @ptrText 0 NULL
OtherTable.NTextValue @valuePointer

update TargetTable set TargetTable.TargetFloatValue = convert(float,
convert (nvarchar, TargetTable.NTextValue)) where TargetTable.ID =
@id

update TargetTable set TargetTable.NTextValue = NULL where
TargetTable.ID = @id

However, even though this appears to be working, I am using an
intermediate table field which I have to clear after I have converted
and move the value over and I do not want to execute 3 update queries
if I can only do one.

Your help is appreciated,

Cally
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
Cally (ze**********@hotmail.com) writes:
I would like to convert a field from ntext field found in one database
table to float field found in another database table. The reason why I
want to do this is a long one.

I have tried the following and playing around with the following:

declare @valuePointer varbinary(16)

<Row cursor logic to initialize @valuePointer to be a pointer to the
source ntext field>

update TargetFloatTable set TargetFloatTable.TargetFloatValue =
CAST(CAST(@valuePointer AS nvarchar) AS float)
where TargetFloatTable.Id = @Id

but is not working for me.


You are trying to convert the text pointer to float - that is not
very likely to succeed. Here is an example that works:

CREATE TABLE nt (a int, n ntext)
go
INSERT nt (a, n) values (1, '9.234E30')
go
select convert(float, convert(nvarchar, n)) from nt

Jul 23 '05 #4
Nevermind ... I am still testing but I think that I figured it out.

@valueText is nvarchar local variable that I populate from the
OtherTable.NTextValue in the fetch cursor

update TargetTable set TargetTable.TargetTableFloatValue =
convert(float, @valueText) where TargetTable.ID = @id

I realize that it is not ideal to do all of this converting and mixing
cursor-fetch-updatetext and regular updates. My problem of having 3
value fields (datetime, ntext, and float) that only one should be
updated based on DataType field seems to call for it.

Is there a better way to do this?

"Cally" <ze**********@hotmail.com> wrote in message news:<11*********************@o13g2000cwo.googlegr oups.com>...
Hello,

I would like to convert a field from ntext field found in one database
table to float field found in another database table. The reason why I
want to do this is a long one.

I have tried the following and playing around with the following:

declare @valuePointer varbinary(16)

<Row cursor logic to initialize @valuePointer to be a pointer to the
source ntext field>

update TargetFloatTable set TargetFloatTable.TargetFloatValue =
CAST(CAST(@valuePointer AS nvarchar) AS float)
where TargetFloatTable.Id = @Id

but is not working for me.

Hoping someone out there can help.

Thanks,

Cally

Jul 23 '05 #5
Katie (ze**********@hotmail.com) writes:
So based on your suggestion, I tried this:

update TargetTable set TargetTable.TargetFloatValue = (select
convert(float, convert(nvarchar, OtherTable.NTextValue)) from
OtherTable where OtherTable.ID = @id) where TargetTable.ID = @id

And I get the following error:

"Error converting data type nvarchar to float."
Which in itself is not that surprising. I would expect most ntext
columns in this would have values that are not convertible to float.
Which really surprises me because it is basically what you have
written and because the following works:

updatetext TargetTable TargetTable.NTextValue @ptrText 0 NULL
OtherTable.NTextValue @valuePointer

update TargetTable set TargetTable.TargetFloatValue = convert(float,
convert (nvarchar, TargetTable.NTextValue)) where TargetTable.ID =
@id


Then again, it's funny if one thing works but not the other.

I don't have that much ideas, but then I have not seen the contents
of these ntext columns. Nor do I know why you are doing all this. What
I can suggest is that do a select on the two tables, and see whether you
have the same data. You could also post a script with CREATE TABLE
statements for the table and INSERT statements with some data that
fails you.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by sunshinenewton | last post: by
7 posts views Thread by nephish | last post: by
15 posts views Thread by Kay Schluehr | last post: by
7 posts views Thread by Partho | last post: by
11 posts views Thread by redefined.horizons | last post: by
4 posts views Thread by Yasin Cepeci | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.