如何在SQL server 2008中根据长度而不是分隔符拆分数据。

Question

我有一个拆分数据的查询，但它是基于分隔符的。我的问题是：

DECLARE @xml xml,@str varchar(100),@delimiter varchar(10)
SET @str= 'VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
select @xml
SELECT a.value('.','varchar(10)') as value FROM @xml.nodes('X') as X(a)

我想根据长度拆分数据，即10个字符。

提前谢谢。

Answer 1

就像..。(编辑:分隔符现在安全)

DECLARE @str varchar(100)
DECLARE @splitlen tinyint, @hasSeparator bit;

SELECT @str= 'VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR,VINAYKUMAR'
SELECT @splitlen = 10, @hasSeparator = 1

;WITH cNumber AS 
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY C1.column_id) * (@splitlen+@hasSeparator) - @splitlen + (1-@hasSeparator) AS Number
    FROM
        sys.columns C1, sys.columns C2
)
SELECT
    SUBSTRING(@str, Number, @splitlen-@hasSeparator)
FROM
    cNumber
WHERE
    Number < LEN (@str)



SELECT @str= 'VINAYKUMARVINAYKUMARVINAYKUMARVINAYKUMARVINAYKUMARVINAYKUMARVINAYKUMAR'
SELECT @splitlen = 10, @hasSeparator = 0
;WITH cNumber AS 
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY C1.column_id) * (@splitlen+@hasSeparator) - @splitlen + (1-@hasSeparator) AS Number
    FROM
        sys.columns C1, sys.columns C2
)
SELECT
    SUBSTRING(@str, Number, @splitlen - @hasSeparator)
FROM
    cNumber
WHERE
    Number < LEN (@str)

Answer 2

使用SUBSTRING