查找担保圈-step2-拆分成员表函数 

 1 USE [test]
 2 GO
 3 /****** Object:  UserDefinedFunction [dbo].[tf_split_char]    Script Date: 2019/7/8 14:39:15 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 ALTER FUNCTION [dbo].[tf_split_char]
 9 (
10     @cstring NVARCHAR(MAX), --被拆分的字符串
11     @csplit NVARCHAR(1)     --分隔符
12 )
13 RETURNS @t TABLE
14 (
15     id INT IDENTITY(1, 1) PRIMARY KEY,
16     cchar NVARCHAR(MAX) NOT NULL
17 )
18 AS
19 BEGIN
20     DECLARE @ilocation INT = 0;
21     DECLARE @inextlocation INT = 1;
22     DECLARE @ilen INT;
23     WHILE @inextlocation > 0
24     BEGIN
25         SET @inextlocation = CHARINDEX(@csplit, @cstring, @ilocation + 1);
26         SET @ilen = CASE
27                         WHEN @inextlocation > 0 THEN
28                             @inextlocation
29                         ELSE
30                             LEN(@cstring) + 1
31                     END - @ilocation - 1;
32         INSERT INTO @t
33         (
34             cchar
35         )
36         VALUES
37         (SUBSTRING(@cstring, @ilocation + 1, @ilen));
38         SET @ilocation = @inextlocation;
39     END;
40     RETURN;
41 END;

 

posted on 2019-07-08 14:40  hold_on_up  阅读(125)  评论(0)  编辑  收藏  举报