将一些金额转换为数字:
250.5 -> 250
200万 -> 200
12万(rmb)-> 12
12.5元 -> 12就是去除小数点后或是非数字字符。谢谢大家。本来想稍微改下过去找的过程的……
但好像没有吧小数点后面的数字也去掉。
CREATE function fn_num(
@str varchar(60)
)
returns varchar(60)
as
begin
declare @r varchar(60)
set @r=''
while PATINDEX('%[0-9]%',@str)>0
begin
set @str=stuff(@str,1,PATINDEX('%[0-9]%',@str)-1,'')
if PATINDEX('%[^0-9]%',@str)>0
begin
set @r=@r+left(@str,PATINDEX('%[^0-9]%',@str)-1)
set @str=stuff(@str,1,PATINDEX('%[^0-9]%',@str)-1,'')
end
else
begin
set @r=@r+@str
set @str=''
end
end
if ISNUMERIC(@r)=0
begin
set @r = '0'
end
return @r
end
250.5 -> 250
200万 -> 200
12万(rmb)-> 12
12.5元 -> 12就是去除小数点后或是非数字字符。谢谢大家。本来想稍微改下过去找的过程的……
但好像没有吧小数点后面的数字也去掉。
CREATE function fn_num(
@str varchar(60)
)
returns varchar(60)
as
begin
declare @r varchar(60)
set @r=''
while PATINDEX('%[0-9]%',@str)>0
begin
set @str=stuff(@str,1,PATINDEX('%[0-9]%',@str)-1,'')
if PATINDEX('%[^0-9]%',@str)>0
begin
set @r=@r+left(@str,PATINDEX('%[^0-9]%',@str)-1)
set @str=stuff(@str,1,PATINDEX('%[^0-9]%',@str)-1,'')
end
else
begin
set @r=@r+@str
set @str=''
end
end
if ISNUMERIC(@r)=0
begin
set @r = '0'
end
return @r
end
insert @a select '250.5'
union all select '200万'
union all select '12万(rmb)'
union all select '12.5元'select left(a, patindex('%[^0-9]%',a)-1) from @a where patindex('%[^0-9]%',a)>0
/*
-----------------
250
200
12
12(所影响的行数为 4 行)
*/
insert into tb values('250.5')
insert into tb values('200万')
insert into tb values('12万')
insert into tb values('12.5元')
gocreate function getnewstr
(@oldstr varchar(100))
returns varchar(100)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^0-9]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
goupdate tb
set col = dbo.getnewstr(col)
where col like('%[^0-9]%')select * from tbdrop table tb
drop function getnewstr
/*
col
--------------------
2505
200
12
125
(所影响的行数为 4 行)
*/
create table tb(col varchar(20))
insert into tb values('250.5')
insert into tb values('200万')
insert into tb values('12万')
insert into tb values('12.5元')
gocreate function fn_cast (@a varchar(200)) returns decimal(18,3)
as
begin
declare @i int
declare @temp int
declare @str varchar(20)
set @i=patindex('%[0-9]%',@a)while 1=1
begin
if isnumeric(substring(@a,@i+1,1))=0 break
set @i=@i+1
endset @str=substring(@a+'a',patindex('%[0-9]%',@a),@i) return (cast(@str as decimal(18,3)))
end
select
*,
'value'=dbo.fn_cast(col)
from tb/*
250.5 250.500
200万 200.000
12万 12.000
12.5元 12.500*/
if exists(select * from sysobjects where id=object_id('up_vchToint'))
drop procedure up_vchToint
Go
Create proc up_vchToint
@vchInit varchar(100),--初始字符串
@intResult int output--转换成int类型的结果值
as
declare @intn int--字符串的位数
declare @vchn varchar(1)--第n位的字符
declare @vchResult varchar(100)--结果字符串
set @intn=1
set @vchResult=''
while @intn<=len(@vchInit)
begin
set @vchn=substring(@vchInit,@intn,1)
if ascii(@vchn)>=ascii('0') and ascii(@vchn)<=ascii('9')
begin
set @vchResult=@vchResult + @vchn
set @intn=@intn+1
end
else
break
end
select @intResult=cast(@vchResult as int)
return @intResult执行代码:
declare @intResult int
exec up_vchToint '145一5454dddsd.8',@intResult output
select @intResult自己写的,楼主试试。
declare @test table(test varchar(11))
insert @test
select '250.5' union all
select '200万' union all
select '12万(rmb)' union all
select '12.5元'--select test from @test where isnumeric(test)=0 group by test
update @test set test=replace(test,'万','')
update @test set test=replace(test,'元','')
update @test set test=replace(test,'(rmb)','')
--...其它替换select case when charindex('.',test)=0 then test else left(test,charindex('.',test)-1) end from @test
/*
250
200
12
12
*/
select parsename('250.5万',2)