转几个实用的sql函数
--将阿拉伯数字的钱转换成大写金额
CREATE function to_up(@num numeric(14,2))
returns varchar(100)
as
begin
declare @ndata varchar(20),@cdata varchar(100)
declare @nstr varchar(10),@zflag bit,@t varchar(10),@i int
set @ndata=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)),14)
set @cdata=''
set @zflag=0;set @i=1
while @i<=14
begin
set @nstr=substring(@ndata,@i,1)
if @nstr<>' '
begin
--数字
set @nstr=SUBSTRING('零壹贰叁肆伍陆柒捌玖',CAST(@nstr AS int)+1,1)
set @t=SUBSTRING('仟佰拾亿仟佰拾萬仟佰拾圆角分',@i,1)
if @nstr='零'
begin
if @t in ('亿','萬','圆')
set @cdata=@cdata+@t
if @zflag=0
set @zflag=1
end
else
begin
if @zflag=1
begin
set @cdata=@cdata+'零'+@nstr+@t
set @zflag=0
end
else
set @cdata=@cdata+@nstr+@t
end
end
set @i=@i+1
end
IF @num<0
SET @cdata='（负数）'+@cdata
IF @num=0
SET @cdata='零圆'
IF @nstr='零'
SET @cdata=@cdata+'整'
RETURN(replace(@cdata,'壹拾','拾'))
end
--将日期转换为大写
CREATE function dbo.f_GetUpData(@date varchar(10))
returns varchar(100)
begin
declare @table table (num int ,value varchar(10))
insert into @table
select 0,'零' union
select 1,'一' union
select 2,'二' union
select 3,'三' union
select 4,'四' union
select 5,'五' union
select 6,'六' union
select 7,'七' union
select 8,'八' union
select 9,'九'
declare @getdate varchar(100),@month varchar(10),@day varchar(10)
set @getdate=@date
select @getdate= replace(@getdate,num,value)
from @table a
select @getdate=stuff(stuff(@getdate,charindex('-',@getdate),1,'年'),charindex(' -',stuff(@getdate,charindex('-',@getdate),1,'年')),1,'月')+'日'
set @month=substring(@getdate,charindex('年',@getdate)+1,charindex('月',@getdate)-charindex('年',@getdate)-1)
set @day=substring(@getdate,charindex('月',@getdate)+1,charindex('日',@getdate)-charindex('月',@getdate)-1)
select @getdate=case when (len(@month)=2 and left(@month,1)='零') or len(@month)=1 then left(@getdate,5)+right(@month,1)+'月'+@day+'日'
when len(@month)=2 and left(@month,1)='一' and right(@month,1)='零' then left(@getdate,5)+'十月'+@day+'日'
when len(@month)=2 and left(@month,1)='一' and right(@month,1)<>'零' then left(@getdate,5)+'十'+right(@month,1)+'月'+@day+'日'
end
set @month=substring(@getdate,charindex('年',@getdate)+1,charindex('月',@getdate)-charindex('年',@getdate)-1)
set @day=substring(@getdate,charindex('月',@getdate)+1,charindex('日',@getdate)-charindex('月',@getdate)-1)
select @getdate=case
when (len(@day)=2 and left(@day,1)='零') or len(@day)=1 then left(@getdate,5)+@month+'月'+right(@day,1)+'日'
when len(@day)=2 and left(@day,1)='一' and right(@day,1)='零' then left(@getdate,5)+@month+'月十日'
when len(@day)=2 and left(@day,1)='一' and right(@day,1)<>'零' then left(@getdate,5)+@month+'月十'+right(@day,1)+'日'
when len(@day)=2 and left(@day,1)='二' and right(@day,1)='零' then left(@getdate,5)+@month+'月二十日'
when len(@day)=2 and left(@day,1)='二' and right(@day,1)<>'零' then left(@getdate,5)+@month+'月二十'+right(@day,1)+'日'
when len(@day)=2 and left(@day,1)='三' and right(@day,1)='零' then left(@getdate,5)+@month+'月三十日'
when len(@day)=2 and left(@day,1)='三' and right(@day,1)<>'零' then left(@getdate,5)+@month+'月三十'+right(@day,1)+'日'
end
return @getdate
end
--在字符中提取中文字符，个人认为很实用
if object_id('fnExtractChinese') is not null
drop function fnExtractChinese
GO
----创建字符串提取函数
create function fnExtractChinese(@string varchar(100))
returns varchar(100)
as
begin
declare @str varchar(100)
set @str = ''
while PATINDEX('%[吖-座]%',@String) 0
begin
set @str = @str + substring(@String,PATINDEX('%[吖-座]%',@String),1)
set @String = stuff(@String,PATINDEX('%[吖-座]%',@String),1,'')
end
return @str
end
GO
4、来自邹建
CREATE FUNCTION f_GetPY(@str nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @py TABLE(
ch char(1),
hz1 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS,
hz2 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS)
INSERT @py SELECT 'A',N'吖',N'鏊'
UNION ALL SELECT 'B',N'八',N'簿'
UNION ALL SELECT 'C',N'嚓',N'错'
UNION ALL SELECT 'D',N'哒',N'跺'
UNION ALL SELECT 'E',N'屙',N'贰'
UNION ALL SELECT 'F',N'发',N'馥'
UNION ALL SELECT 'G',N'旮',N'过'
UNION ALL SELECT 'H',N'铪',N'蠖'
UNION ALL SELECT 'J',N'丌',N'竣'
UNION ALL SELECT 'K',N'咔',N'廓'
UNION ALL SELECT 'L',N'垃',N'雒'
UNION ALL SELECT 'M',N'妈',N'穆'
UNION ALL SELECT 'N',N'拿',N'糯'
UNION ALL SELECT 'O',N'噢',N'沤'
UNION ALL SELECT 'P',N'趴',N'曝'
UNION ALL SELECT 'Q',N'七',N'群'
UNION ALL SELECT 'R',N'蚺',N'箬'
UNION ALL SELECT 'S',N'仨',N'锁'
UNION ALL SELECT 'T',N'他',N'箨'
UNION ALL SELECT 'W',N'哇',N'鋈'
UNION ALL SELECT 'X',N'夕',N'蕈'
UNION ALL SELECT 'Y',N'丫',N'蕴'
UNION ALL SELECT 'Z',N'匝',N'做'
DECLARE @i int
SET @i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)
WHILE @i>0
SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1),ch)
,@i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)
FROM @py
WHERE SUBSTRING(@str,@i,1) BETWEEN hz1 AND hz2
RETURN(@str)
END
GO
