SQL Server实用自定义函数_sql server 创建函数

除法处理

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)
/* 函数说明：除法处理
 * 参数1：v_dividend 被除数
 * 参数2：v_dividen 除数
 * 参数3：v_onzero 当除数为0时返回的值
 * 参数4：v_point 保留小数位数,NULL值则返回默认位数
 */
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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 |
1
2
3
4
5
6
7
8
9
10

FindInSet

1. 创建语句

CREATE FUNCTION F_FindInSet(
	@str varchar(255),
	@strset varchar(4000),
	@split varchar(10))
RETURNS int
/* 函数说明：查找字符串在指定集合中是否存在，返回值大于0即存在
 * 参数1：v_str 查找的字符串
 * 参数2：v_str_set 目标字符串集合
 * 参数3：v_split 字符串集合分隔符
 */
BEGIN
	RETURN charindex(@split + @str + @split , @split + @strset + @split)
END;
1
2
3
4
5
6
7
8
9
10
11
12
13

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|
1
2
3
4
5
6
7
8

查找字符串(第n次)下标

1. 创建语句

CREATE FUNCTION F_IndexOf(
	@v_str VARCHAR(1000),
	@v_value VARCHAR(50),
	@v_n INT
) RETURNS INT 
/* 函数说明：查找字符串在目标字符串中，第n次出现的下标
 * 参数1：v_str   	目标字符串
 * 参数2：v_value 	查找的字符串
 * 参数3：v_n 		第n次出现
 */
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)
			 --如果没有找到就返回0,比如abcabc其中a中出现在2次@v_n=3的话就返回0
			IF(@v_pos=0) RETURN 0
		END  
	RETURN @v_pos
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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|
1
2
3
4
5
6
7
8

数值转RMB

1. 创建语句

CREATE FUNCTION F_MoneyToRMB (@money numeric(14,2))
RETURNS nvarchar(32) 
AS
/* 函数说明：数值转RMB
 * 参数1：money   	需要转为RMB大写的数值，千亿及以内正数数值
 */
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, '亿万', '亿')    --当金额为x亿零万时去掉万
	IF @money=0 set @money_chn='零圆整'    --当金额为零时返回'零圆整'
	IF @n_chn='0' set @money_chn=@money_chn+'整'    --当金额末尾为零分时以'整'结尾

	RETURN @money_chn    --返回大写金额
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

2. 调用示例

SELECT 
	dbo.F_MoneyToRMB(10051.48) RMB_money1,
	dbo.F_MoneyToRMB(14699) RMB_money2

RMB_money1          |RMB_money2          |
--------------------+--------------------+
壹万零伍拾壹圆肆角捌分|壹万肆仟陆佰玖拾玖圆整|
1
2
3
4
5
6
7

split截取

1. 创建语句

CREATE FUNCTION dbo.F_SplitOfIndex(
  	@v_String nvarchar(max),	
  	@v_cut nvarchar(10),		
  	@v_index int	
)RETURNS nvarchar(1024)
AS
/* 函数说明：查找字符串在指定集合中是否存在，返回值大于0即存在
 * 参数1：v_String 要分割的字符串(集合)
 * 参数2：v_cut 分隔符号
 * 参数3：v_index 取第几个元素
 */
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;
  	-- 如果分隔符出现次数,即拆分后元素数量小于元素下标，返回NULL
  	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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

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
2
3
4
5
6
7
8
9

日期与毫秒数互转

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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
2
3
4
5
6
7
8
9

字符串添加前缀空格

1. 创建语句

CREATE FUNCTION Func_LeftAddSpace(
    @v_str VARCHAR(1000),
    @v_n INT
) RETURNS VARCHAR(1000) 
AS 
/* 函数说明：字符串前面添加指定数量的空格
 * 参数1：v_str 	原字符串
 * 参数2：v_n 	空格数量
 */
BEGIN
    DECLARE @v_spaces char(120) = ''
    RETURN (SELECT LEFT(@v_spaces , @v_n) + @v_str)
END;
1
2
3
4
5
6
7
8
9
10
11
12
13

2. 调用示例

SELECT 
	dbo.Func_LeftAddSpace('Hello SQL',10) s10,
	dbo.Func_LeftAddSpace('Hello SQL',5) s5

s10                |s5            |
-------------------+--------------+
          Hello SQL|     Hello SQL|
1
2
3
4
5
6
7