How to convert from ntext to float?

[Cally]

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

[Ross Presser]

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

[Erland Sommarskog]

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

[Katie]

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

[Katie]

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

[Erland Sommarskog]

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