Split text every 250 characters and save

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

SQL Server 2000 Forums

SQL Server Development (2000)

Split text every 250 characters and save

Author

Topic

jenzhen99
Starting Member

3 Posts

Posted - 2012-11-30 : 08:48:14

Hi,

I have a column with long text, which I would like to split into columns text1, text2, etc by making a split every 250 characters. Then I would like to save text1, text2, etc together with one other variable as a new table. I've tried to write a script (I guess I'll just need 2 lines really?) with which I can implement that in SQL Browser, but haven't succeeded so far. Would anyone know how to do this?
Thanks so much, JZ

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-30 : 08:59:44

;with ce as
(
select i=1, s = substring(str,1,250), id from tbl
union all
select i=i+1, s = substring(str,(i-1)*250+251,250), id from tbl t join cte c on c.var = t.var where len(str) >= ,(i-1)*250+251
)
insert newtbl
select var, seq = i, str
from cte

You can pivot this if you want it in columns but you would then need a maximum size for the string.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-30 : 09:04:50

Another approach, ion case you are really on SQL 2000 and don't have CTE's available to you

DECLARE @x VARCHAR(255)= 'abcdefghkijlmn';

DECLARE @chunksize INT = 3; -- change to what you want
SELECT
	@chunksize*number+1,
	@chunksize,
	SUBSTRING(@x,@chunksize*number+1,@chunksize) 
FROM
	MASTER..spt_values
WHERE
	[type] = 'P'
	AND @chunksize*number <= LEN(@x);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources