将 SQL Server 中的字符串拆分为最大长度，将每个字符串作为一行返回 [英] Split string in SQL Server to a maximum length, returning each as a row

问题描述

有没有办法将字符串(从特定列)拆分为 n 个字符而不破坏单词，每个结果都在自己的行中?

Is there a way to split a string (from a specific column) to n-number chars without breaking words, with each result in its own row?

示例:

2012-04-24 Change request #3 for the contract per terms and conditions and per John Smith in the PSO department  Customer states terms should be Net 60 not Net 30.  Please review signed contract for this information.

结果:

2012-04-24 Change request #3 for the contract per terms and conditions and per John Smith in the
PSO department  Customer states terms should be Net 60 not Net 30.
Please review signed contract for this information.

我知道我可以使用 charindex 来查找最后一个空格，但我不确定如何获取剩余的空格并将它们作为行返回.

I know I can use charindex to find the last space, but im not sure how i can get the remaining ones and return them as rows.

推荐答案

尝试这样的事情.可能你可以创建一个如下实现的SQL函数.

Try something like this. May be your can create a SQL function of following implementation.

DECLARE @Str VARCHAR(1000)
SET @Str = '2012-04-24 Change request #3 for the contract per terms and conditions and per John Smith in the PSO department  Customer states terms should be Net 60 not Net 30.  Please review signed contract for this information.'

DECLARE @End INT
DECLARE @Split INT

SET @Split = 100

declare @SomeTable table
(
  Content varchar(3000)
)


WHILE (LEN(@Str) > 0)
BEGIN
    IF (LEN(@Str) > @Split)
    BEGIN
        SET @End = LEN(LEFT(@Str, @Split)) - CHARINDEX(' ', REVERSE(LEFT(@Str, @Split)))
        INSERT INTO @SomeTable VALUES (RTRIM(LTRIM(LEFT(LEFT(@Str, @Split), @End))))
        SET @Str = SUBSTRING(@Str, @End + 1, LEN(@Str))
    END
    ELSE
    BEGIN
        INSERT INTO @SomeTable VALUES (RTRIM(LTRIM(@Str)))
        SET @Str = ''
    END
END

SELECT *
FROM @SomeTable

输出如下:

2012-04-24 Change request #3 for the contract per terms and conditions and per John Smith in the
PSO department  Customer states terms should be Net 60 not Net 30.  Please review signed contract
for this information.

这篇关于将 SQL Server 中的字符串拆分为最大长度，将每个字符串作为一行返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！