sql数据类型转换问题，将nvarchar类型转换成任意的数字类型都行，在线等。。。。

[我是一只小小小的菜鸟]

原始数据如下：

if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (ID INT IDENTITY(1,1),COL VARCHAR(100))
insert into #A
select '12345.123456' union all
select '33345.123456' union all
select '45645.123456'  union all
select '23445.123456'  union all
select '13245.123456' union all
select '3345.123456' 
select * from #A

只要将上面的数字转换成数字类型任意一种都行int、decimal(18, 6)、float、等都可以！

[我是一只小小小的菜鸟]

[Quote=引用 4 楼 dawugui 的回复:]
SQL code
create table #A (ID INT IDENTITY(1,1),COL VARCHAR(100))
insert into #A
select '12345.123456' union all
select '33345.123456' union all
select '45645.123456' union all
select '23445.123……
[/Quote]

这个根本行不通的 哥哥，2005上面显示的错误是：从数据类型 nvarchar 转换为 numeric 时出错。

[oO寒枫Oo]

楼主 你的col字段里面应该有一些不是数字样式的 如：123.abc ，1.2.3 ？

[oO寒枫Oo]

加个条件了试试

create table #A (ID INT IDENTITY(1,1),COL nVARCHAR(100))
insert into #A
select '12345.123456' union all
select '33345.123456' union all
select '45645.123456'  union all
select '23445.123456'  union all
select '13245.123456a' union all
select '3345.123456.' 

select id , cast(col as decimal(18,6)) col from #A where   ISNUMERIC(col)=1 

drop table #A

[我是一只小小小的菜鸟]

[Quote=引用 8 楼 lxpbs8851 的回复:]
楼主 你的col字段里面应该有一些不是数字样式的 如：123.abc ，1.2.3 ？
[/Quote]

没有 全部是数字类型的。高手。

[oO寒枫Oo]

其实有一些数字类型的是无法用ISNUMERIC(col)=1来进行筛选的 如：, + - .123 等等
没加限制条件而直接转换就会出现：从数据类型 nvarchar 转换为 numeric 时出错。

select id , cast(col as decimal(18,6)) col from #A where
ISNUMERIC(col)=1 and (col  not like  '%[^0-9]%' or charindex('.',col)>1 )   and charindex('.','0'+col)<>2     and  charindex(',',col)<1 and  charindex('+',col)<1 and  charindex('-',col)<1 

[ArchMageWeb]

select convert(decimal(18, 6),ID) as id from #a

[guguda2008]

if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (ID INT IDENTITY(1,1),COL VARCHAR(100))
insert into #A
select '12345.123456' union all
select '33345.123456' union all
select '45645.123456'  union all
select '23445.123456'  union all
select '13245.123456' union all
select '3345.123456' 
select COL,CONVERT(FLOAT,COL)+1 from #A
/*
12345.123456	12346.123456
33345.123456	33346.123456
45645.123456	45646.123456
23445.123456	23446.123456
13245.123456	13246.123456
3345.123456	3346.123456
*/

[fpzgm]

if object_id('tb') is not null drop table tb
create table tb (ID INT IDENTITY(1,1),COL VARCHAR(100))
insert into tb
select '12345.123456' union all
select '33345.123456' union all
select '45645.123456'  union all
select '23445.123456'  union all
select '13245.123456' union all
select '3345.123456'


select id,CAST(COL as decimal(18,6)) as col from tb
/*
id	col
1	12345.123456
2	33345.123456
3	45645.123456
4	23445.123456
5	13245.123456
6	3345.123456
*/
select id,CAST(COL as float) as col from tb
/*
id	col
1	12345.123456
2	33345.123456
3	45645.123456
4	23445.123456
5	13245.123456
6	3345.123456
*/
select id,cast(CAST(COL as float) as int) as col from tb
/*
id	col
1	12345
2	33345
3	45645
4	23445
5	13245
6	3345
*/

[dawugui]

create table #A (ID INT IDENTITY(1,1),COL VARCHAR(100))
insert into #A
select '12345.123456' union all
select '33345.123456' union all
select '45645.123456'  union all
select '23445.123456'  union all
select '13245.123456' union all
select '3345.123456' 

select id , cast(col as decimal(18,6)) col from #A

drop table #A

/*
id          col                  
----------- -------------------- 
1           12345.123456
2           33345.123456
3           45645.123456
4           23445.123456
5           13245.123456
6           3345.123456

（所影响的行数为 6 行）
*/

[abuying]

select id , cast(col as int) col from #A
select id , cast(col as decimal(18,2)) col from #A
--float还不如使用real,
select id , cast(col as real) col from #A
select id , str(col,18,2) col from #A

[我是一只小小小的菜鸟]

谢谢各位哈 谢谢 等下测试完毕之后马上给分。

[我是一只小小小的菜鸟]

已经结贴 谢谢各位！