除法处理
1. 创建语句
CREATE FUNCTION F_Divide(
@v_dividend decimal(30,8),
@v_dividen decimal(30,8),
@v_zero decimal(30,8),
@v_point int
) RETURNS decimal(30,8)
BEGIN
DECLARE @v_res decimal(30,8);
SET @v_res = CASE @v_dividen WHEN 0 THEN @v_zero ELSE @v_dividend/@v_dividen END;
IF ( @v_point IS NOT NULL )
BEGIN
SET @v_res = ROUND( @v_res, @v_point )
END ;
RETURN @v_res;
END;
2. 调用示例
SELECT
dbo.F_Divide(100, 30, 0 , NULL) r1,
dbo.F_Divide(100, 30, 0 , 2) r2,
dbo.F_Divide(100, 3 , 0 , -1) r3,
dbo.F_Divide(100, 3 , 0 , 0) r4,
dbo.F_Divide(100, 0 , 1 , NULL) r5
r1 |r2 |r3|r4|r5|
3.33333333|3.33|30|33|1 |
FindInSet
1. 创建语句
CREATE FUNCTION F_FindInSet(
@str varchar(255),
@strset varchar(4000),
@split varchar(10))
RETURNS int
BEGIN
RETURN charindex(@split + @str + @split , @split + @strset + @split)
END;
2. 调用示例
SELECT
dbo.F_FindInSet('def','abc,def,ghi,jkl,mn',',') c1,
dbo.F_FindInSet('a','a-b-c-d-e-f','-') c2,
dbo.F_FindInSet('e','abcdef','') c3
c1|c2|c3|
5| 1| 5|
查找字符串(第n次)下标
1. 创建语句
CREATE FUNCTION F_IndexOf(
@v_str VARCHAR(1000),
@v_value VARCHAR(50),
@v_n INT
) RETURNS INT
AS
BEGIN
DECLARE @v_pos int=0
DECLARE @v_i INT =0
WHILE(@v_i<@v_n)
BEGIN
SET @v_i=@v_i+1
SET @v_pos=CHARINDEX(@v_value,@v_str,@v_pos+1)
IF(@v_pos=0) RETURN 0
END
RETURN @v_pos
END;
2. 调用示例
SELECT
dbo.F_IndexOf('abcabcabcabc','ab',1) c1,
dbo.F_IndexOf('abcabcabcabc','ab',2) c2,
dbo.F_IndexOf('abcabcabcabc','ab',10) c3
c1|c2|c3|
1| 4| 0|
数值转RMB
1. 创建语句
CREATE FUNCTION F_MoneyToRMB (@money numeric(14,2))
RETURNS nvarchar(32)
AS
BEGIN
DECLARE
@money_num nvarchar(20),
@money_chn nvarchar(32),
@n_chn nvarchar(1),
@i int
SELECT @money_chn=CASE WHEN @money>=0 THEN '' ELSE NULL END,
@money=abs(@money),
@money_num=stuff(str(@money, 15, 2), 13, 1, ''),
@i=patindex('%[1-9]%', @money_num)
WHILE @i>=1 AND @i<=14
BEGIN
SET @n_chn=substring(@money_num, @i, 1)
IF @n_chn<>'0' OR (substring(@money_num,@i+1,1)<>'0' AND @i NOT IN(4, 8, 12, 14))
SET @money_chn=@money_chn+substring('零壹贰叁肆伍陆柒捌玖', @n_chn+1, 1)
IF @n_chn<>'0' or @i in(4, 8, 12)
SET @money_chn=@money_chn+substring('仟佰拾亿仟佰拾万仟佰拾圆角分',@i,1)
SET @i=@i+1
END
SET @money_chn=replace(@money_chn, '亿万', '亿')
IF @money=0 set @money_chn='零圆整'
IF @n_chn='0' set @money_chn=@money_chn+'整'
RETURN @money_chn
END;
2. 调用示例
SELECT
dbo.F_MoneyToRMB(10051.48) RMB_money1,
dbo.F_MoneyToRMB(14699) RMB_money2
RMB_money1 |RMB_money2 |
壹万零伍拾壹圆肆角捌分|壹万肆仟陆佰玖拾玖圆整|
split截取
1. 创建语句
CREATE FUNCTION dbo.F_SplitOfIndex(
@v_String nvarchar(max),
@v_cut nvarchar(10),
@v_index int
)RETURNS nvarchar(1024)
AS
BEGIN
DECLARE
@v_cutLen INT,
@v_star INT,
@v_next INT,
@v_step INT;
SET @v_String = @v_String + @v_cut;
SET @v_cutLen = LEN(@v_cut);
SET @v_next = 1;
SET @v_step = 1;
IF (LEN(@v_String) - LEN(REPLACE(@v_String,@v_cut,'')))/@v_cutLen < @v_index
BEGIN
RETURN NULL;
END;
WHILE @v_step <= @v_index
BEGIN
SET @v_star = @v_next ;
SET @v_next = CHARINDEX(@v_cut,@v_String,@v_next) + @v_cutLen;
SET @v_step = @v_step + 1;
END;
RETURN SUBSTRING(@v_String, @v_star, @v_next - @v_star - @v_cutLen);
END;
2. 调用示例
SELECT
dbo.F_SplitOfIndex('内科服务--霍乱--西药组','--',3) str1,
dbo.F_SplitOfIndex('内科服务/霍乱/西药组','/',2) str2,
dbo.F_SplitOfIndex('内科服务~霍乱~西药组','~',1) str3,
dbo.F_SplitOfIndex('内科服务~霍乱~西药组','~',4) str4
str1 |str2 |str3 |str4 |
西药组 |霍乱 |内科服务| |
日期与毫秒数互转
1. 创建语句
CREATE FUNCTION dbo.F_DateTimeToLong(@v_date DATETIME2(3))
RETURNS BIGINT
BEGIN
RETURN
(CAST(DATEDIFF(dd, '1970-01-01 00:00:00.000', @v_date) AS BIGINT) * 24 * 60 * 60 * 1000)
+
CAST(DATEDIFF(ms, CONVERT( DATETIME2(3), FORMAT( @v_date, 'yyyy-MM-dd')), @v_date) AS BIGINT)
-
(8 * 60 * 60 * 1000);
END;
CREATE FUNCTION dbo.F_LongToDateTime(@v_long BIGINT)
RETURNS DATETIME2(3)
BEGIN
DECLARE
@v_dd BIGINT,
@v_ms BIGINT,
@v_dt DATETIME2(3);
SET @v_dd = @v_long/1000/60/60/24;
SET @v_ms = @v_long - @v_dd*1000*60*60*24;
SET @v_dt = DATEADD( dd, @v_dd ,'1970-01-01 08:00:00' );
RETURN DATEADD( ms, @v_ms , @v_dt );
END;
2. 调用示例
SELECT
dbo.F_DateTimeToLong('2022-01-01 01:07:32.005') lg1,
dbo.F_DateTimeToLong('2022-10-31') lg2,
dbo.F_LongToDateTime(1640970452005) dt1,
dbo.F_LongToDateTime(1667145600000) dt2
lg1 |lg2 |dt1 |dt2 |
1640970452005|1667145600000|2022-01-01 01:07:32.005|2022-10-31 00:00:00.000|
字符串添加前缀空格
1. 创建语句
CREATE FUNCTION Func_LeftAddSpace(
@v_str VARCHAR(1000),
@v_n INT
) RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @v_spaces char(120) = ''
RETURN (SELECT LEFT(@v_spaces , @v_n) + @v_str)
END;
2. 调用示例
SELECT
dbo.Func_LeftAddSpace('Hello SQL',10) s10,
dbo.Func_LeftAddSpace('Hello SQL',5) s5
s10 |s5 |
Hello SQL| Hello SQL|