Split field separate row results every 70 char

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split field separate row results every 70 char

Author

Topic

tommyz
Starting Member

2 Posts

Posted - 2011-05-10 : 15:18:52

Hi,

I need to return separate results based on data length, not a delimiter.
I need to break down a 490 char field, every 70 characters into a separate row. I have used

substring([Note Text],n,CHARINDEX(' ',[Note Text] + ' |||',n) - n) AS Split
FROM NotesTable as p

CROSS JOIN (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Numbers(n)
WHERE SUBSTRING(' ' + [Note Text], n, 1) = ' '
AND n < LEN([Note Text]) + 1

in the past for returning separate results based on a delimiter, but how to divide the results based on data length?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-10 : 15:25:17

SELECT SUBSTRING([Note TEXT],n*70+1,70) AS Split
FROM NotesTable AS p
CROSS JOIN (SELECT DISTINCT number
FROM master..spt_values
WHERE TYPE = 'P'
AND number BETWEEN 0 AND 100) AS Numbers(n)
WHERE n <= LEN([Note TEXT])/70

tommyz
Starting Member

2 Posts

Posted - 2011-05-10 : 17:46:09

this is good - Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources