declare @t table (colname varchar(6)) insert into @t select '10万' union all select '11000' union all select '1.1万' union all select '1000.1' union all select '201万'select replace(colname,'万','') from @t /* 10 11000 1.1 1000.1 201 */如果要得到数字中的1-9及. 可以参考: http://blog.csdn.net/maco_wang/article/details/6260197
declare @t table (colname varchar(6)) insert into @t select '10万' union all select '11000' union all select '1.1万' union all select '1000.1' union all select '201万'select * from @t where colname not like '%[^0-9]%' and colname not like '%.%'
cast(replace(列名,'万','') as int)
declare @String varchar(1000),@TempNum char(1),@Number varchar(1000) select @String='2001.22万*¥%' select @Number='' while len(@String)>0 begin select @TempNum=substring(@String,1,1) if isnumeric(@TempNum)=1 begin select @Number=@Number+@TempNum end select @String=stuff(@String,1,1,'') end select @Number
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL DROP FUNCTION DBO.GET_NUMBER2 GO CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^0-9-.]%',@S) > 0 BEGIN set @s=stuff(@s,patindex('%[^0-9-.]%',@s),1,'') END RETURN @S END GOdeclare @t table (colname varchar(6)) insert into @t select '10万' union all select '1' union all select '11000' union all select '1.1万' union all select '1000.1' union all select '201万'select DBO.GET_NUMBER2(a.colname) as new,a.colname from @t as a ------------------------------------------(6 行受影响) new colname ------------------- ------- 10 10万 1 1 11000 11000 1.1 1.1万 1000.1 1000.1 201 201万(6 行受影响)
这样?create table #tb(col nvarchar(500)) insert #tb select '10万' union all select '11000' union all select '1.1万' union all select '1000.1' union all select '2 0 1万' union all select '10千万' union all select '11000' union all select '1.1千万' union all select '1000.1' union all select '2 0 1万' union all select '1,,0万' union all select '110550.0' union all select '**1.1万' union all select '10@@@00.1' union all select '2 0 1万'select (select ''+substring(col,number,1) from master..spt_values as v where type='p' and number between 1 and Len(col) and (isnumeric(substring(col,number,1))=1 or substring(col,number,1)='.') for xml path('')) as col from #tb--col ------------- --10 --11000 --1.1 --1000.1 --201 --10 --11000 --1.1 --1000.1 --201 --10 --110550.0 --1.1 --1000.1 --201
#13楼有问题..这样吧.. create table #tb(col nvarchar(500)) insert #tb select '10万' union all select '11000' union all select '1.1万' union all select '1000.1' union all select '2 0 1万' union all select '10千万' union all select '11000' union all select '1.1千万' union all select '1000.1' union all select '2 0 1万' union all select '1,,0万' union all select '110550.0' union all select '**1.1万' union all select '10@@@00.1' union all select '2 0 1万' union all select '8877.5<>?' union all select '8877.5#$@$' union all select '8877.5,./' union all select '8*()877.5' union all select 'HJK8877.5' union all select '中华人民共和国8877.5' select (select ''+substring(col,number,1) from master..spt_values as v where type='p' and number between 1 and Len(col) and (substring(col,number,1) between '0' and '9' or substring(col,number,1)='.') for xml path('')) as col from #tbcol --------- 10 11000 1.1 1000.1 201 10 11000 1.1 1000.1 201 10 110550.0 1.1 1000.1 201 8877.5 8877.5 8877.5. 8877.5 8877.5 8877.5
俺们只是为了测试方便嘛,所以才自己做些测试数据,亲爱的楼主把 #tb 换成你的表,把 col 换成你的列不就行了么?
select (select ''+substring(col,number,1) from master..spt_values as v --把col 换成你的列 where type='p' and number between 1 and Len(col) and (substring(col,number,1) between '0' and '9' or substring(col,number,1)='.') for xml path('')) as col from #tb --把#tb 换成你的表..
declare @t table (colname varchar(6))
insert into @t
select '10万' union all
select '11000' union all
select '1.1万' union all
select '1000.1' union all
select '201万'select replace(colname,'万','') from @t
/*
10
11000
1.1
1000.1
201
*/如果要得到数字中的1-9及.
可以参考:
http://blog.csdn.net/maco_wang/article/details/6260197
declare @t table (colname varchar(6))
insert into @t
select '10万' union all
select '11000' union all
select '1.1万' union all
select '1000.1' union all
select '201万'select * from @t where colname not like '%[^0-9]%' and colname not like '%.%'
cast(replace(列名,'万','') as int)
declare @String varchar(1000),@TempNum char(1),@Number varchar(1000)
select @String='2001.22万*¥%'
select @Number=''
while len(@String)>0
begin
select @TempNum=substring(@String,1,1)
if isnumeric(@TempNum)=1
begin
select @Number=@Number+@TempNum
end
select @String=stuff(@String,1,1,'')
end
select @Number
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9-.]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9-.]%',@s),1,'')
END
RETURN @S
END
GOdeclare @t table (colname varchar(6))
insert into @t
select '10万' union all
select '1' union all
select '11000' union all
select '1.1万' union all
select '1000.1' union all
select '201万'select DBO.GET_NUMBER2(a.colname) as new,a.colname from @t as a
------------------------------------------(6 行受影响)
new colname
------------------- -------
10 10万
1 1
11000 11000
1.1 1.1万
1000.1 1000.1
201 201万(6 行受影响)
insert #tb
select '10万' union all
select '11000' union all
select '1.1万' union all
select '1000.1' union all
select '2 0 1万' union all
select '10千万' union all
select '11000' union all
select '1.1千万' union all
select '1000.1' union all
select '2 0 1万' union all
select '1,,0万' union all
select '110550.0' union all
select '**1.1万' union all
select '10@@@00.1' union all
select '2 0 1万'select (select ''+substring(col,number,1) from master..spt_values as v
where type='p' and number between 1 and Len(col)
and (isnumeric(substring(col,number,1))=1 or substring(col,number,1)='.')
for xml path('')) as col
from #tb--col
-------------
--10
--11000
--1.1
--1000.1
--201
--10
--11000
--1.1
--1000.1
--201
--10
--110550.0
--1.1
--1000.1
--201
create table #tb(col nvarchar(500))
insert #tb
select '10万' union all
select '11000' union all
select '1.1万' union all
select '1000.1' union all
select '2 0 1万' union all
select '10千万' union all
select '11000' union all
select '1.1千万' union all
select '1000.1' union all
select '2 0 1万' union all
select '1,,0万' union all
select '110550.0' union all
select '**1.1万' union all
select '10@@@00.1' union all
select '2 0 1万' union all
select '8877.5<>?' union all
select '8877.5#$@$' union all
select '8877.5,./' union all
select '8*()877.5' union all
select 'HJK8877.5' union all
select '中华人民共和国8877.5' select (select ''+substring(col,number,1) from master..spt_values as v
where type='p' and number between 1 and Len(col)
and (substring(col,number,1) between '0' and '9' or substring(col,number,1)='.')
for xml path('')) as col
from #tbcol
---------
10
11000
1.1
1000.1
201
10
11000
1.1
1000.1
201
10
110550.0
1.1
1000.1
201
8877.5
8877.5
8877.5.
8877.5
8877.5
8877.5
replace(col,'万','')
replace(col,'0','')
...
...
replace(col,'9','')
select (select ''+substring(col,number,1) from master..spt_values as v --把col 换成你的列
where type='p' and number between 1 and Len(col)
and (substring(col,number,1) between '0' and '9' or substring(col,number,1)='.')
for xml path('')) as col
from #tb --把#tb 换成你的表..