Convert full column from nvarchar to float

Question

I have a question how can I convert a SQL Server column with data of type nvarchar to float?

Classic query:

ALTER TABLE <column>
ALTER COLUMN <column> FLOAT;

works only with an empty column.

Is there any way to do this?

Thanks!

Add new float column, copy data, remove old column, rename new column? — jarlh, Jun 23 '16 at 11:17
your code is almost correct just replace the first by tablename — GuidoG, Jun 23 '16 at 11:22
I Have 54 column where I need change data type. I guess I needed something more automatic — michal, Jun 23 '16 at 11:25

score 1 · Answer 1 · edited May 23 '17 at 11:52

Normal classic query works even with columns having data, not only empty ones. But the issue arises when the Nvarchar column holds incompatible data. So best option is to create a new table or create new column and use Try_Convert.. (only from SQL Server 2012)

This works:

create table #t
(
    id nvarchar(3)
)

insert into #t
    select 1
    union all
    select 2

alter table #t
alter column id float--this works

This fails:

create table #t
(
   id nvarchar(3)
)

insert into #t
select 1
union all
select 2

insert into #t
select 'a'

alter table #t
alter column id float--this fails

So best way is to use try_convert and take decision on null values which is incompatible data

create table #t1
(
    id float
)

insert into #t1
    select try_convert(float, id) 
    from #t

Update:

If you are on SQL Server 2008 R2 or below, you can use this technique instead of TRY_Convert. Taken from Mikael excellent answer

declare @T table
(
  Col varchar(50)
)

insert into @T values
('1'),
('1.1'),
('1,1'),
('1a')

select cast('' as xml).value('sql:column("Col") cast as xs:decimal ?', 
                             'decimal(28,10)') as Col
from @T

Output:

Col
-------------
1.0000000000
1.1000000000
NULL
NULL

When I try this script:create table #t1 ( id float ) insert into #t1 select try_convert(float,id) from #t so in new table is only value.. — michal, Jun 23 '16 at 11:40
`TRY_CONVERT` is a **new feature** in SQL Server **2012** and not available to users of an earlier version of SQL Server — marc_s, Jun 23 '16 at 11:49
Thanks @marc_s,since user didnt mentioned the version,i didnt paid mcuh attention to it.will update my answer — TheGameiswar, Jun 23 '16 at 11:51

score 0 · Answer 2 · answered Jun 23 '16 at 11:20

0

You should use the following syntax:

ALTER TABLE table_name
ALTER COLUMN column_name datatype

answered Jun 23 '16 at 11:20

Pepijn de Jong

46
4

Its the same problem. – michal Jun 23 '16 at 11:21

Convert full column from nvarchar to float

2 Answers2