复制代码 代码如下:
–cast 和 convert 函数 percentage
declare @dec decimal(5,3), @var varchar(10),@hun decimal(5,1)
set @dec=0.025
set @hun=@dec*100
set @var=cast(@hun as varchar(20))+’%’
select @var
—小数转化为百分数函数 getpercentagestring
—涂聚文 geovin du
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[getpercentagestring]’) and xtype in (n’fn’, n’if’, n’tf’))
drop function [dbo].[getpercentagestring]
go
create function getpercentagestring
(
@dec decimal(10,3)
)
returns varchar(20)
as
begin
declare @var varchar(10),@hun decimal(10,1)
set @hun=@dec*100
set @var=cast(@hun as varchar(20))+’%’
return @var
end
go
–测试数据 涂聚文 geovin du
select [dbo].[getpercentagestring] (0.05)
select [dbo].[getpercentagestring] (0.25)
select [dbo].[getpercentagestring] (1.25)
–1. 查询字符串中是否包含非数字字符
select patindex(‘%[^0-9]%’, ‘1235×461’)
select patindex(‘%[^0-9]%’, ‘12350461’)
–2. 查询字符串中是否包含数字字符
select patindex(‘%[0-9]%’, ‘suyllgoo’)
select patindex(‘%[0-9]%’, ‘suyllg0o’)
declare @dec decimal(10,4), @var varchar(10),@hun decimal(10,4),@i int,@k int,@s varchar(20)
set @var=’5.23%’
–select @k=len(@var)
–select @s=left(@var,@k-1)
select @s=right(@var,1)
–select @s
select @i=charindex(‘%’,@s)
if @i>0
begin
set @var=replace(@var,’%’,”)
set @hun=cast(@var as decimal(10,4))
set @dec=@hun/100
select @dec
end
else
begin
select @dec=null –‘无效数据’
end
–百分数转化为小数函数 getpercentagenumber
—涂聚文 geovin du
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[getpercentagenumber]’) and xtype in (n’fn’, n’if’, n’tf’))
drop function [dbo].[getpercentagenumber]
go
create function getpercentagenumber
(
@var varchar(10)
)
returns decimal(10,4)
as
begin
declare @dec decimal(10,4), @hun decimal(10,4),@i int,@k int,@s varchar(20)
select @s=right(@var,1)
–select @s
select @i=charindex(‘%’,@s)
if @i>0
begin
set @var=replace(@var,’%’,”)
set @hun=cast(@var as decimal(10,4))
set @dec=@hun/100
–select @dec
end
else
begin
select @dec=null –‘无效数据’
end
return @dec
end
go
–测试数据 涂聚文 geovin du
select [dbo].[getpercentagenumber] (‘5.23%’)
select [dbo].[getpercentagenumber] (‘%0.23’)