How to SPLIT the data in SQL server 2008 based on the length not on delimiter
I have a query to split the data but it is based on a delimiter. My query is:
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)
I want split the data based on length i.e 10 characters.
Thanks in 开发者_如何转开发advance.
Something like... (Edit: separator safe now)
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)
Use SUBSTRING
精彩评论