sql-server-2008 - 在 SQL Server 2008 中将字符串拆分为固定长度的部分

Question

我正在使用 Split string 方法，我已经完成了将字符串拆分成固定长度的 block ，但问题是，它打破了这个词，我想保持这个词的完整性，并以“SPACE”字符为基础，分解。

下面我也附上了带有测试结果的函数。请指导或指导我如何使用。

/*
Select dbo.SplitFixedLengthString('This is me , I am going to split this    string in such a way that it will not break any word, rather it keeps word',16)
*/

CREATE FUNCTION [dbo].[SplitFixedLengthString]
(
  @string NVARCHAR(MAX) ,
  @stringlength INT
)
RETURNS NVARCHAR(MAX)
AS 
BEGIN
    DECLARE @tempStr NVARCHAR(MAX)
    DECLARE @finalString NVARCHAR(MAX)
    IF LEN(@string) > 0
        AND @stringlength > 0 
        BEGIN                
            SELECT  @tempStr = ''                
            DECLARE @i INT 
            SET @i = 1

            WHILE @i <= LEN(@string) 
                BEGIN
                    SELECT  @tempStr = @tempStr + SUBSTRING(@string, @i,@stringlength) + (CHAR(13) + CHAR(10))                        
                    SET @i = @i + @stringlength
                END

        END

    RETURN @tempStr
END

测试结果:

---

This is me , I a
m going to split
this string in 
such a way that 
it will not brea
k any word, rath
er it keeps word

(1 行受影响)正如我们所看到的，输出的第一行正在拆分单词“am”，就像第 4,5 行一样。

请给我建议解决方案。

这是我的努力:

--DECLARE @x VARCHAR(32) = 'xyzxyzyyythgetdghydgsh j';
--SELECT LEN(@x) - CHARINDEX(' ', REVERSE(@x)) + 1;


DECLARE @string NVARCHAR(MAX) 
SELECT @string = 'This is me , I am going to split this string in such a way that it will not break any word, rather it keeps word' 
--SELECT @string = 'This is me,I am going to Trim off things in such a way that it will add newline having '    
DECLARE      @stringlength INT
SELECT @stringlength = 11

BEGIN
    DECLARE @tempStr NVARCHAR(MAX)
    DECLARE @finalString NVARCHAR(MAX) = ''
    IF LEN(@string) > 0
        AND @stringlength > 0 
        BEGIN                
            SELECT  @tempStr = ''                
            DECLARE @start_index INT = 1
            DECLARE @last_index INT = 0               
            DECLARE @lastindex INT  = LEN(@string)-1                
            WHILE @start_index <  @lastindex
                BEGIN                                          
                    SELECT  @tempStr  = SUBSTRING(@string, @start_index,@stringlength )                                                          
                    IF RIGHT(@tempStr,1) = ' '                        
                    BEGIN   

                        SET @finalString = @finalString + @tempStr + (CHAR(13) + CHAR(10))
                        SET @start_index = @start_index + @stringlength
                    END
                    ELSE
                       BEGIN                                                   
                        --SELECT @last_index = LEN(@tempStr) - CHARINDEX(' ', REVERSE(@tempStr)) + 1;
                        SELECT @last_index  = LEN(@tempStr) - CHARINDEX(' ',REVERSE(@tempStr))                          
                        IF @last_index = 0
                        BEGIN
                            SET @finalString = @finalString + LTRIM(SUBSTRING(@tempStr, 1,LEN(@tempStr)+1))+  (CHAR(13) + CHAR(10))
                            SET @start_index = @start_index + LEN(@tempStr)                             
                        END
                        ELSE
                        BEGIN                            
                            SET @finalString = @finalString + LTRIM(SUBSTRING(@tempStr, 1,@last_index +1))+  (CHAR(13) + CHAR(10))
                            SET @start_index = @start_index + @last_index                               
                            END                                                   
                    END                        
                    IF @start_index + @stringlength >= @lastindex
                      BEGIN                                                                         
                        SET @finalString = @finalString + LTRIM(SUBSTRING(@string, @start_index,(@lastindex - @start_index)+1))+  (CHAR(13) + CHAR(10))
                        SET @start_index = @start_index + (@lastindex - @start_index)                           
                    END


                END

        END

    SELECT @finalString
END

这是输出

 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This is me 
, I am 
going to 
split 
this 
string in 
such a way 
that it 
will not 
break any 
word, 
rather it 
keeps wor

(1 row(s) affected)

Answer 1

经过一点努力，我编写了这段代码。它工作正常，虽然有点棘手。

ALTER FUNCTION [dbo].[SplitFixedLengthString]
(
  @string NVARCHAR(MAX) ,
  @stringlength INT
)
RETURNS NVARCHAR(MAX)
AS 
BEGIN
    DECLARE @tempStr NVARCHAR(MAX)
    DECLARE @finalString NVARCHAR(MAX) = ''
    IF LEN(@string) > 0
        AND @stringlength > 0 
        BEGIN                
            SELECT  @tempStr = ''                
            DECLARE @start_index INT = 1
            DECLARE @last_index INT = 0               
            DECLARE @lastindex INT  = LEN(@string)  
            DECLARE @NextChar VARCHAR(1)    = ''        
            WHILE @start_index <  @lastindex
                BEGIN                                          
                    SELECT  @tempStr  = SUBSTRING(@string, @start_index,@stringlength )                                                                                   
                    IF RIGHT(@tempStr,1) = ' '                        
                    BEGIN   

                        SET @finalString = @finalString + @tempStr + (CHAR(13) + CHAR(10))
                        SET @start_index = @start_index + @stringlength
                    END
                    ELSE
                    BEGIN                                                                       
                        SELECT @last_index  = LEN(@tempStr) - CHARINDEX(' ',REVERSE(@tempStr))                                              
                        SET @NextChar = SUBSTRING(@string, @start_index +LEN(@tempStr),1)                                                               
                        IF  @last_index = 0 AND @NextChar = ' '                         
                        BEGIN                           
                            SET @finalString = @finalString + LTRIM(SUBSTRING(@tempStr, 1,LEN(@tempStr)-1))+(CHAR(13)    )
                            SET @start_index = @start_index + LEN(@tempStr) 
                        END     
                        IF  @last_index = 0 AND @NextChar <> ' '                            
                        BEGIN                           
                            SET @finalString = @finalString + LTRIM(SUBSTRING(@tempStr, 1,LEN(@tempStr)-1))+  '_'+ (CHAR(13)   )
                            SET @start_index = @start_index + LEN(@tempStr) -1  
                        END 
                        ELSE IF (@last_index)  = LEN(@tempStr)
                        BEGIN                               
                            SET @finalString = @finalString + LTRIM(SUBSTRING(@tempStr, 1,LEN(@tempStr)-1))+  '_'+ (CHAR(13)   )
                            SET @start_index = @start_index + LEN(@tempStr) -1                          
                        END         
                        ELSE IF @last_index <> 0 AND @NextChar = ' '
                        BEGIN                               
                            SET @finalString = @finalString + LTRIM(SUBSTRING(@tempStr, 1,LEN(@tempStr)))+  (CHAR(13) )
                            SET @start_index = @start_index + LEN(@tempStr)                             
                        END
                        ELSE IF (LEN(@tempStr) - @last_index) = 2 AND @NextChar <> ' '
                        BEGIN                               
                            SET @finalString = @finalString + LTRIM(SUBSTRING(@tempStr, 1,LEN(@tempStr)-1))+  (CHAR(13)   )
                            SET @start_index = @start_index + LEN(@tempStr) -1                          
                        END
                        ELSE IF (@last_index) <> 0 AND @NextChar <> ' '
                        BEGIN                               
                            SET @finalString = @finalString + LTRIM(SUBSTRING(@tempStr, 1,LEN(@tempStr)-1))+  '_'+ (CHAR(13)   )
                            SET @start_index = @start_index + LEN(@tempStr) -1                          
                        END
                        ELSE
                        BEGIN                                                        
                            SET @finalString = @finalString + LTRIM(SUBSTRING(@tempStr, 1,@last_index ))+  (CHAR(13)  )
                            SET @start_index = @start_index + @last_index                               
                        END                                                   
                    END                        
                    IF @start_index + @stringlength >= @lastindex
                    BEGIN                        
                        SET @finalString = @finalString + LTRIM(SUBSTRING(@string, @start_index,(@lastindex - @start_index)+1))+  (CHAR(13)  )
                        SET @start_index = @start_index + (@lastindex - @start_index)                           
                    END


                END

        END    
        RETURN @finalString  
END