sql server 将一个字符串分割成多行

不念不忘少年蓝@ 2023-10-05 11:38 23阅读 0赞

创建 func\_splitstring 函数

Create FUNCTION [dbo].[func_splitstring]  
        (  
          @str NVARCHAR(MAX) ,  
          @split VARCHAR(10)  
        )  
    RETURNS @t TABLE ( c1 VARCHAR(100) )  
    AS   
        BEGIN  
            DECLARE @i INT  
            DECLARE @s INT  
            SET @i = 1  
            SET @s = 1  
            WHILE ( @i > 0 )   
                BEGIN      
                    SET @i = CHARINDEX(@split, @str, @s)  
                    IF ( @i > 0 )   
                        BEGIN  
                            INSERT  @t  
                                    ( c1 )  
                            VALUES  ( SUBSTRING(@str, @s, @i - @s) )  
                        END     
                    ELSE   
                        BEGIN  
                            INSERT  @t  
                                    ( c1 )  
                            VALUES  ( SUBSTRING(@str, @s, LEN(@str) - @s + 1) )  
                        END  
                    SET @s = @i + 1     
                END  
            RETURN  
        END

调用实例：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xvb2tpbmdUb21vcnJvdw_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xvb2tpbmdUb21vcnJvdw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201014134316236.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xvb2tpbmdUb21vcnJvdw==,size_16,color_FFFFFF,t_70