SQL Server中自定义函数：用指定的分隔符号分割字符串

Myth丶恋晨 2021-07-05 00:28 440阅读 0赞

微软`SQL Server`数据库中包含了很多内置的函数，入下图：  
![在这里插入图片描述][20200330111712838.png]  
![在这里插入图片描述][20200330111704378.png]  
它们用于处理日期、数学、元数据、字符串等。

其中最为常用的就是处理字符串，里面包含了`CharIndex()`等函数，非常方便使用。

但是对于特殊字符串的处理，比如：`ISBN`号 ‘`978-7-5007-7234-7`’，如果想获取第三个与第四个分割符号之间的数字，

那么SQL 内置函数无法直接做到。这时就需要自定义函数。下面自定义三个函数，用于处理特殊的字符串。

## 一、按指定符号分割字符串，返回分割后的元素个数 ##

ALTER FUNCTION [dbo].[Fun_GetStrArrayLength]
    (
        @originalStr VARCHAR(1024),    --要分割的字符串
        @split       VARCHAR(10)       --分隔符号
    )
    RETURNS INT
    AS
    BEGIN
        DECLARE @location INT;  --定义起始位置
        DECLARE @start INT;     --定义从第几个开始
        DECLARE @length INT;    --定义变量，用于接收计算元素的个数
        
        SET @originalStr = LTRIM(RTRIM(@originalStr));    --去除字符串左右两侧的空格
        
        SET @location = CHARINDEX(@split, @originalStr);  --分割符号在字符串中第一次出现的位置（索引从1开始计数）
        
        SET @length = 1;
        
        WHILE @location <> 0
        BEGIN
            SET @start = @location + 1;
            SET @location = CHARINDEX(@split, @originalStr, @start);
            SET @length = @length + 1;
        END
        RETURN @length;
    END

调用函数：`select dbo.Fun_GetStrArrayLength('978-7-5007-7234-7','-')`  
结果：5

## 二、按指定符号分割字符串，返回分割后指定索引的第几个元素，像数组一样方便 ##

ALTER FUNCTION [dbo].[Fun_GetStrArrayStrOfIndex]
    (
        @originalStr     VARCHAR(1024),  --要分割的字符串
        @split           VARCHAR(10),    --分隔符号
        @index           INT             --取第几个元素
    )
    RETURNS VARCHAR(1024)
    AS
    BEGIN
        DECLARE @location INT; --定义第一次出现分隔符号的位置
        DECLARE @start INT;    --定义开始位置
        DECLARE @next INT;     --定义下一个位置
        DECLARE @seed INT;     --定义分割符号的长度
        
        SET @originalStr = LTRIM(RTRIM(@originalStr));    --去除字符串左右2侧空格
        SET @start = 1;
        SET @next = 1;
        SET @seed = LEN(@split);
        
        SET @location = CHARINDEX(@split, @originalStr);  --第一次出现分隔符号的位置
        
        WHILE @location <> 0
              AND @index > @next
        BEGIN
            SET @start = @location + @seed;
            SET @location = CHARINDEX(@split, @originalStr, @start);
            SET @next = @next + 1;
        END
        
        IF @location = 0
            BEGIN
                SELECT @location = LEN(@originalStr) + 1;
            END
    
        --存在两种情况：
        --1、字符串不存在分隔符号。
        --2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
        
        RETURN SUBSTRING(@originalStr, @start, @location -@start);
    END

调用函数：`select dbo.Fun_GetStrArrayStrOfIndex('978-7-5007-7234-7','-',4)`

结果：7234

## 三、像数组一样遍历字符串中的元素 ##

ALTER FUNCTION [dbo].[Fun_SplitStr]
    (
        @originalStr      VARCHAR(8000), --要分割的字符串
        @split varchar(100)              --分隔符号
    )
    RETURNS @temp TABLE(Result VARCHAR(100))
    AS
    BEGIN
        DECLARE @result AS VARCHAR(100);   --定义变量用于接收单个结果 
        
        SET @originalStr = @originalStr + @split ;  
          
        WHILE (@originalStr <> '')
        BEGIN
            SET @result = LEFT(@originalStr, CHARINDEX(@split, @originalStr, 1) -1) ;  
            
            INSERT @temp VALUES(@result) ;  
            
            --STUFF()函数用于删除指定长度的字符，并可以在指定的起点处插入另一组字符。
            SET @originalStr = STUFF(@originalStr, 1, CHARINDEX(@split, @originalStr, 1), '');
        END 
        RETURN
    END

调用示例：`select * from dbo.Fun_SplitStr('978-7-5007-7234-7','-')`

结果：

978
    
    7
    
    5007
    
    7234
    
    7

[20200330111712838.png]: /images/20210704/acc4c30164da476da660cf1212b5983d.png
[20200330111704378.png]: /images/20210704/68ac64a31299446c99f65034274f4683.png